SQL Server allows you to query the data (which is stored in the table) in XML format by using FOR XML clause. The FOR XML clause has 3 modes: AUTO, RAW, and PATH that you can choose based on your needs.

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 take a look at the table and examples below to understand the difference between those three modes in FOR XML clause.

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 AUTO

Each row in the table becomes one row of XML element. Table name Students becomes tag, 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" />

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.

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" />

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" />

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>

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. Everything is returned as elements and nothing is returned as attributes.

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>

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>

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>