Post
Ask Question

FOR XML In SQL Sever

Introduction

In this article, we will discuss FOR XML Path Clause in SQL Server. Beginners, Intermediate, or professionals can use this article.

We will cover,

  1. Use Case
  2. FOR XML
  3. AUTO Mode with FOR XML 
  4. PATH Mode with FOR XML
  5. Difference between AUTO Mode and PATH Mode.
  6. An Elements Directive with FOR XML 
  7. RAW Mode with FOR XML 
  8. EXPLICIT Mode with FOR XML

Let's start this article with Use Case,

USE CASE

In today's world, XML plays a vital role in development, and we often get XML data in our databases. FOR XML Path Clause can help us to deal with XML data in SQL Server.

Suppose we have a requirement to return data in XML format from the database; we should go with FOR XML clause.

FOR XML in SQL Server

FOR XML clause can use to convert existing data to XML format. It can also join or concatenate multiple columns into a single row.

FOR XML Clause has below 4 Modes that decide the shape of the XML – result.

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

Syntax  

[ FOR { BROWSE | <XML> } ]  
<XML> ::=  
XML   
    {   
      { RAW [ ('ElementName') ] | AUTO }   
        [   
           <CommonDirectives>   
           [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
           [ , ELEMENTS [ XSINIL | ABSENT ]   
        ]  
      | EXPLICIT   
        [   
           <CommonDirectives>   
           [ , XMLDATA ]   
        ]  
      | PATH [ ('ElementName') ]   
        [   
           <CommonDirectives>   
           [ , ELEMENTS [ XSINIL | ABSENT ] ]  
        ]  
     }   
  
 <CommonDirectives> ::=   
   [ , BINARY BASE64 ]  
   [ , TYPE ]  
   [ , ROOT [ ('RootName') ] ]

AUTO MODE With FOR XML

FOR XML AUTO and FOR XML PATH are the simplest ways to convert SQL data into XML. In the below example, we will discuss FOR XML AUTO Clause.

We will create Member Table and insert a few rows to show the demo in this article.

CREATE TABLE Member  
(  
  MemberId int identity(1,1) primary key,  
  FirstName varchar(100),  
  LastName varchar(100),  
  Address varchar(100),
  City varchar(100),  
  State varchar(100), 
  PhoneNumber int  
)

Now Insert a few rows,

INSERT INTO Member
VALUES('Kirtesh','Shah','TestAddress','Vadodara','Gujarat','1234567890'),
('Rajesh','Shah','SuratAddress','Surat','Gujarat','2134567894'),
('Raj','Patel','TestAddress2','Vadodara','Gujarat','176347896'),
('Megha','Shah','SuratAddress3','Kucha','Gujarat','457896321'),
('Viushal','Patel','TestAddress4','Bhuj','Gujarat','325468796'),
('Mahesh','Shah','SuratAddress5','Sura','Gujarat','186347896')

First, we will execute the below SELECT Query and see the result,

SELECT * FROM Member

OUTPUT

AUTO MODE With FOR XML

Now we will discuss an example of FOR XML AUTO,

SELECT * FROM Member FOR XML AUTO

OUTPUT

AUTO MODE With FOR XML

Let's click on XML shown above,

AUTO MODE With FOR XML

XML OUTPUT

<Member MemberId="22" FirstName="Kirtesh" LastName="Shah" Address="TestAddress" City="Vadodara" State="Gujarat" PhoneNumber="1234567890" />
<Member MemberId="23" FirstName="Rajesh" LastName="Shah" Address="SuratAddress" City="Surat" State="Gujarat" PhoneNumber="2134567894" />
<Member MemberId="24" FirstName="Raj" LastName="Patel" Address="TestAddress2" City="Vadodara" State="Gujarat" PhoneNumber="176347896" />
<Member MemberId="25" FirstName="Megha" LastName="Shah" Address="SuratAddress3" City="Kucha" State="Gujarat" PhoneNumber="457896321" />
<Member MemberId="26" FirstName="Viushal" LastName="Patel" Address="TestAddress4" City="Bhuj" State="Gujarat" PhoneNumber="325468796" />
<Member MemberId="27" FirstName="Mahesh" LastName="Shah" Address="SuratAddress5" City="Sura" State="Gujarat" PhoneNumber="186347896" />

PATH Mode with FOR XML

Let's use the same query with FOR XML PATH.

SELECT * FROM Member FOR XML PATH

OUTPUT

PATH Mode with FOR XML

XML OUTPUT

<row>
  <MemberId>22</MemberId>
  <FirstName>Kirtesh</FirstName>
  <LastName>Shah</LastName>
  <Address>TestAddress</Address>
  <City>Vadodara</City>
  <State>Gujarat</State>
  <PhoneNumber>1234567890</PhoneNumber>
</row>
<row>
  <MemberId>23</MemberId>
  <FirstName>Rajesh</FirstName>
  <LastName>Shah</LastName>
  <Address>SuratAddress</Address>
  <City>Surat</City>
  <State>Gujarat</State>
  <PhoneNumber>2134567894</PhoneNumber>
</row>
<row>
  <MemberId>24</MemberId>
  <FirstName>Raj</FirstName>
  <LastName>Patel</LastName>
  <Address>TestAddress2</Address>
  <City>Vadodara</City>
  <State>Gujarat</State>
  <PhoneNumber>176347896</PhoneNumber>
</row>
<row>
  <MemberId>25</MemberId>
  <FirstName>Megha</FirstName>
  <LastName>Shah</LastName>
  <Address>SuratAddress3</Address>
  <City>Kucha</City>
  <State>Gujarat</State>
  <PhoneNumber>457896321</PhoneNumber>
</row>
<row>
  <MemberId>26</MemberId>
  <FirstName>Viushal</FirstName>
  <LastName>Patel</LastName>
  <Address>TestAddress4</Address>
  <City>Bhuj</City>
  <State>Gujarat</State>
  <PhoneNumber>325468796</PhoneNumber>
</row>
<row>
  <MemberId>27</MemberId>
  <FirstName>Mahesh</FirstName>
  <LastName>Shah</LastName>
  <Address>SuratAddress5</Address>
  <City>Sura</City>
  <State>Gujarat</State>
  <PhoneNumber>186347896</PhoneNumber>
</row>

Difference Between FOR XML and XML Path

Let's compare the output of FOR XML AUTO and FOR XML PATH,

Difference Between FOR XML and FOR XML Path

As per the above image,

  • FOR XML AUTO – Structured XML where each column is an attribute.
  • FOR XML PATH – Structured XML where each record is an element, and the column becomes a nested element.

Elements Directive with FOR XML 

In the above query, added the name of the element like below

SELECT * FROM Member FOR XML PATH ('Member')

OUTPUT

Elements Directive with FOR XML

XML OUTPUT

<Member>
  <MemberId>22</MemberId>
  <FirstName>Kirtesh</FirstName>
  <LastName>Shah</LastName>
  <Address>TestAddress</Address>
  <City>Vadodara</City>
  <State>Gujarat</State>
  <PhoneNumber>1234567890</PhoneNumber>
</Member>
<Member>
  <MemberId>23</MemberId>
  <FirstName>Rajesh</FirstName>
  <LastName>Shah</LastName>
  <Address>SuratAddress</Address>
  <City>Surat</City>
  <State>Gujarat</State>
  <PhoneNumber>2134567894</PhoneNumber>
</Member>
<Member>
  <MemberId>24</MemberId>
  <FirstName>Raj</FirstName>
  <LastName>Patel</LastName>
  <Address>TestAddress2</Address>
  <City>Vadodara</City>
  <State>Gujarat</State>
  <PhoneNumber>176347896</PhoneNumber>
</Member>
<Member>
  <MemberId>25</MemberId>
  <FirstName>Megha</FirstName>
  <LastName>Shah</LastName>
  <Address>SuratAddress3</Address>
  <City>Kucha</City>
  <State>Gujarat</State>
  <PhoneNumber>457896321</PhoneNumber>
</Member>
<Member>
  <MemberId>26</MemberId>
  <FirstName>Viushal</FirstName>
  <LastName>Patel</LastName>
  <Address>TestAddress4</Address>
  <City>Bhuj</City>
  <State>Gujarat</State>
  <PhoneNumber>325468796</PhoneNumber>
</Member>
<Member>
  <MemberId>27</MemberId>
  <FirstName>Mahesh</FirstName>
  <LastName>Shah</LastName>
  <Address>SuratAddress5</Address>
  <City>Sura</City>
  <State>Gujarat</State>
  <PhoneNumber>186347896</PhoneNumber>
</Member>

You must notice that instead of <row>, it is showing <Member>.

RAW Mode with FOR XML

Let's see below query and XML Output to get a better idea.

SELECT * FROM Member FOR XML RAW

OUTPUT

RAW Mode with FOR XML

XML OUTPUT

<row MemberId="22" FirstName="Kirtesh" LastName="Shah" Address="TestAddress" City="Vadodara" State="Gujarat" PhoneNumber="1234567890" />
<row MemberId="23" FirstName="Rajesh" LastName="Shah" Address="SuratAddress" City="Surat" State="Gujarat" PhoneNumber="2134567894" />
<row MemberId="24" FirstName="Raj" LastName="Patel" Address="TestAddress2" City="Vadodara" State="Gujarat" PhoneNumber="176347896" />
<row MemberId="25" FirstName="Megha" LastName="Shah" Address="SuratAddress3" City="Kucha" State="Gujarat" PhoneNumber="457896321" />
<row MemberId="26" FirstName="Viushal" LastName="Patel" Address="TestAddress4" City="Bhuj" State="Gujarat" PhoneNumber="325468796" />
<row MemberId="27" FirstName="Mahesh" LastName="Shah" Address="SuratAddress5" City="Sura" State="Gujarat" PhoneNumber="186347896" />

EXPLICIT MODE with FOR XML

Suppose you want XML results in your format; EXPLICIT is the right choice. It will give you the flexibility to generate your result in your format.

Syntax

<ELEMENT>!<TAG>!<ATTRIBUTE>[!<DIRECTIVE>]

We can use the above syntax to generate XML in our desired format.

That's all for this article. I hope you learn and find it helpful.


Similar Articles