Sunday, January 5, 2014

FOR XML in SQL SERVER

Let's explore how to get output of sql query into XML.we will run series of queries with incremental changes and check each one's output.

Create a table Student as follows

CREATE TABLE [dbo].[Student](
    [StudentCode] [varchar](50) NOT NULL,
    [StudentName] [varchar](200) NOT NULL,
    [StudentAddress] [varchar](300) NULL,
    [ContactNo] [varchar](20) NULL,
    [EmailAddress] [varchar](100) NULL,
PRIMARY KEY CLUSTERED
(
    [StudentCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now add some records into tables as follows
a)    Insert into Student (StudentCode,StudentName,StudentAddress,ContactNo,EmailAddress)
      values(‘1’,’sangram s’,’mumbai’,’ 9890868324’,’ sangram.s@xmail.com’)
b)    Insert into Student (StudentCode,StudentName,StudentAddress,ContactNo,EmailAddress)
      values(‘2’,’sagar s’,’ devgad’,’ 9890865432,’ sangram.s@xmail.com’)
c)    Insert into Student (StudentCode,StudentName,StudentAddress,ContactNo,EmailAddress)
      values(‘3’,’ sachin s’,’ banglore’,’ 9898677866,’ sachin.s@zmail.com’)
d)    Insert into Student (StudentCode,StudentName,StudentAddress,ContactNo,EmailAddress)
       values(‘4’,’ swapnil s’,’ banglore’,’ 9890657356,’ swapnil.s@zmail.com’)

Lets see what is in table
1)    select * from student
Output:

StudentCode
StudentName
StudentAddress
ContactNo
EmailAddress
1
sangram s
mumbai
9890868324
sangram.s@xmail.com
2
sagar s
devgad
9890865432
sagar.s@ymail.com
3
sachin s
banglore
9898677866
sachin.s@zmail.com
4
swapnil s
banglore
9890657356
swapnil.s@amail.com
    Now let’s see output of each of following query with for xml option

2)    select * from student FOR XML RAW

Output:
<row StudentCode="1" StudentName="sangram desai" StudentAddress="mumbai" ContactNo="9890868345" EmailAddress="sangram.desai@ymail.com" />
<row StudentCode="2" StudentName="sagar desai" StudentAddress="devgad" ContactNo="9890868344" EmailAddress="sagar.desai@ymail.com" />
<row StudentCode="3" StudentName="sachin desai" StudentAddress="banglore" ContactNo="9890868343" EmailAddress="sachin.desai@ymail.com" />
<row StudentCode="4" StudentName="swapnil sardeshmukh" StudentAddress="banglore" ContactNo="9890868342" EmailAddress="swapnil.sardeshmukh@ymail.com" />

Each row in normal output becomes an element in xml,element name remains ‘row’

3)    select * from student FOR XML RAW ('Student')
Output:
<Student StudentCode="1" StudentName="sangram desai" StudentAddress="mumbai" ContactNo="9890868345" EmailAddress="sangram.desai@ymail.com" />
<Student StudentCode="2" StudentName="sagar desai" StudentAddress="devgad" ContactNo="9890868344" EmailAddress="sagar.desai@ymail.com" />
<Student StudentCode="3" StudentName="sachin desai" StudentAddress="banglore" ContactNo="9890868343" EmailAddress="sachin.desai@ymail.com" />
<Student StudentCode="4" StudentName="swapnil sardeshmukh" StudentAddress="banglore" ContactNo="9890868342" EmailAddress="swapnil.sardeshmukh@ymail.com" />
Each row in normal output becomes an element in xml,element name becomes ‘student’ that we specified

4)    select * from student FOR XML RAW ('Student'),ROOT
Output:
<root>
  <Student StudentCode="1" StudentName="sangram desai" StudentAddress="mumbai" ContactNo="9890868345" EmailAddress="sangram.desai@ymail.com" />
  <Student StudentCode="2" StudentName="sagar desai" StudentAddress="devgad" ContactNo="9890868344" EmailAddress="sagar.desai@ymail.com" />
  <Student StudentCode="3" StudentName="sachin desai" StudentAddress="banglore" ContactNo="9890868343" EmailAddress="sachin.desai@ymail.com" />
  <Student StudentCode="4" StudentName="swapnil sardeshmukh" StudentAddress="banglore" ContactNo="9890868342" EmailAddress="swapnil.sardeshmukh@ymail.com" />
</root>

Each row in normal output becomes an element in xml,element name becomes ‘student’ that we specified in query,the whole xml get wrapped within ‘root’ element.

5)    select * from student FOR XML RAW ('Student'),ROOT('Students')
Output:
<Students>
  <Student StudentCode="1" StudentName="sangram desai" StudentAddress="mumbai" ContactNo="9890868345" EmailAddress="sangram.desai@ymail.com" />
  <Student StudentCode="2" StudentName="sagar desai" StudentAddress="devgad" ContactNo="9890868344" EmailAddress="sagar.desai@ymail.com" />
  <Student StudentCode="3" StudentName="sachin desai" StudentAddress="banglore" ContactNo="9890868343" EmailAddress="sachin.desai@ymail.com" />
  <Student StudentCode="4" StudentName="swapnil sardeshmukh" StudentAddress="banglore" ContactNo="9890868342" EmailAddress="swapnil.sardeshmukh@ymail.com" />
</Students>

  Here root element’s name can be customized that we can specify in query here it is ‘Students’

6) select * from student FOR XML RAW ('Student'),ROOT('Students'),elements
Output:
<Students>
  <Student>
    <StudentCode>1</StudentCode>
    <StudentName>sangram desai</StudentName>
    <StudentAddress>mumbai</StudentAddress>
    <ContactNo>9890868345</ContactNo>
    <EmailAddress>sangram.desai@ymail.com</EmailAddress>
  </Student>
  <Student>
    <StudentCode>2</StudentCode>
    <StudentName>sagar desai</StudentName>
    <StudentAddress>devgad</StudentAddress>
    <ContactNo>9890868344</ContactNo>
    <EmailAddress>sagar.desai@ymail.com</EmailAddress>
  </Student>
  <Student>
    <StudentCode>3</StudentCode>
    <StudentName>sachin desai</StudentName>
    <StudentAddress>banglore</StudentAddress>
    <ContactNo>9890868343</ContactNo>
    <EmailAddress>sachin.desai@ymail.com</EmailAddress>
  </Student>
  <Student>
    <StudentCode>4</StudentCode>
    <StudentName>swapnil sardeshmukh</StudentName>
    <StudentAddress>banglore</StudentAddress>
    <ContactNo>9890868342</ContactNo>
    <EmailAddress>swapnil.sardeshmukh@ymail.com</EmailAddress>
  </Student>
</Students>

Here root element’s name is set as  ‘Students’ as each column value is set as new element instead of attribute of column level element.