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.