SQL Server allows us to query the data (which is stored in the table) in XML format by using FOR XML clause. This clause has several modes: AUTO, RAW, and PATH that we can choose based on our needs. This article explains how to use these 3 modes of FOR XML clause with examples.

eXtensible Markup Language (XML) itself is a text-based structured data and usually used in exchanging data over the web, to send data between one system to another system. XML is the foundation of several web technologies such as AJAX (XML is the X in AJAX) and Web Services.

Let’s say we have a Student table containing the following data and we want to output the data in XML format.

Table: Students

Name Major GPA
Olivia Mardhiyah Graphic Design 3.40
Cahyanto Saefullah Computer Science 3.20
Novi Nuraini Computer Science 2.94

FOR XML Examples

Take a look at the following examples to understand the difference between those three modes in FOR XML clause.

FOR XML AUTO

A. AUTO mode basic example

Each row in the table becomes one row of an XML element. Table name Students becomes tag, and columns become attributes.

SELECT	Fullname AS Name, Major, GPA 
FROM	Students 
FOR XML AUTO
Result
<Students Name="Olivia Mardhiyah" Major="Graphic Design" GPA="3.40" />
<Students Name="Cahyanto Saefullah" Major="Computer Science" GPA="3.20" />
<Students Name="Novi Nuraini" Major="Computer Science" GPA="2.94" />

B. AUTO mode with a custom root element

If you want to give a root element named Students.

SELECT	Fullname AS Name, Major, GPA 
FROM	Students Student
FOR XML AUTO, ROOT('Students')
Result
<Students>
  <Student Name="Olivia Mardhiyah" Major="Graphic Design" GPA="3.40" />
  <Student Name="Cahyanto Saefullah" Major="Computer Science" GPA="3.20" />
  <Student Name="Novi Nuraini" Major="Computer Science" GPA="2.94" />
</Students>

FOR XML RAW

The result is similar to FOR XML AUTO. The difference is that the tag name is just row by default.

C. RAW mode basic example

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML RAW
Result
<row Name="Olivia Mardhiyah" Major="Graphic Design" GPA="3.40" />
<row Name="Cahyanto Saefullah" Major="Computer Science" GPA="3.20" />
<row Name="Novi Nuraini" Major="Computer Science" GPA="2.94" />

D. RAW mode with a tag name

However, you can override the default tag name for the element. Let’s change the default tag name row to Student.

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML RAW ('Student')
Result
<Student Name="Olivia Mardhiyah" Major="Graphic Design" GPA="3.40" />
<Student Name="Cahyanto Saefullah" Major="Computer Science" GPA="3.20" />
<Student Name="Novi Nuraini" Major="Computer Science" GPA="2.94" />

E. RAW mode with a root element

Another example with a root element named Students.

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML RAW ('Student'), ROOT ('Students')
Result
<Students>
  <Student Name="Olivia Mardhiyah" Major="Graphic Design" GPA="3.40" />
  <Student Name="Cahyanto Saefullah" Major="Computer Science" GPA="3.20" />
  <Student Name="Novi Nuraini" Major="Computer Science" GPA="2.94" />
</Students>

F. RAW mode with changing attributes to elements

You can also change the attributes to elements by adding ELEMENTS

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML RAW ('Student'), ROOT ('Students'), ELEMENTS
Result
<Students>
  <Student>
    <Name>Olivia Mardhiyah</Name>
    <Major>Graphic Design</Major>
    <GPA>3.40</GPA>
  </Student>
  <Student>
    <Name>Cahyanto Saefullah</Name>
    <Major>Computer Science</Major>
    <GPA>3.20</GPA>
  </Student>
  <Student>
    <Name>Novi Nuraini</Name>
    <Major>Computer Science</Major>
    <GPA>2.94</GPA>
  </Student>
</Students>

FOR XML PATH

The XML is formatted as elements.

G. PATH mode basic example

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML PATH
Result
<row>
  <Name>Olivia Mardhiyah</Name>
  <Major>Graphic Design</Major>
  <GPA>3.40</GPA>
</row>
<row>
  <Name>Cahyanto Saefullah</Name>
  <Major>Computer Science</Major>
  <GPA>3.20</GPA>
</row>
<row>
  <Name>Novi Nuraini</Name>
  <Major>Computer Science</Major>
  <GPA>2.94</GPA>
</row>

H. PATH mode with an element name

You can also override the default element row. Let’s change the default element row to Student.

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML PATH ('Student')
Result
<Student>
  <Name>Olivia Mardhiyah</Name>
  <Major>Graphic Design</Major>
  <GPA>3.40</GPA>
</Student>
<Student>
  <Name>Cahyanto Saefullah</Name>
  <Major>Computer Science</Major>
  <GPA>3.20</GPA>
</Student>
<Student>
  <Name>Novi Nuraini</Name>
  <Major>Computer Science</Major>
  <GPA>2.94</GPA>
</Student>

I. PATH mode with a custom root element

Then with a root element named Students.

SELECT	Fullname AS Name, Major, GPA 
FROM	Students
FOR XML PATH ('Student'), ROOT('Students')
Result
<Students>
  <Student>
    <Name>Olivia Mardhiyah</Name>
    <Major>Graphic Design</Major>
    <GPA>3.40</GPA>
  </Student>
  <Student>
    <Name>Cahyanto Saefullah</Name>
    <Major>Computer Science</Major>
    <GPA>3.20</GPA>
  </Student>
  <Student>
    <Name>Novi Nuraini</Name>
    <Major>Computer Science</Major>
    <GPA>2.94</GPA>
  </Student>
</Students>