FOR XML Path ( SQL Server )

Artikel
01/18/2012

For xml path gives you flexibility to generate xml from your database in the desired structure with less code .
Believe me once you learn the xml path you will love this feature .

So lets start with a simple customer table , the schema of the table is as follows :

If if just write for xml auto query like this :

select * from dbo.Customer for xml auto

, we will get the xml like this :

<dbo.Customer CustomerId="1" FirstName="Priyanka" LastName="Srivastava" Address="lucknow" Email="priyanka@yahoo.com" PhoneNumber="23232232" />

<dbo.Customer CustomerId="2" FirstName="Manish " LastName="Sharma" Address="mumbai" Email="manish@gmail.com" PhoneNumber="232323" />

<dbo.Customer CustomerId="3" FirstName="Kanak" LastName="Srivastava" Address="Delhi" Email="kanak@hotmail.com" PhoneNumber="2165273" />

<dbo.Customer CustomerId="4" FirstName="Rachita" LastName="Pandey" Address="Bhopal" Email="Rachita@abc.com" PhoneNumber="7868768" />

<dbo.Customer CustomerId="5" FirstName="Jitender" LastName="Singh" Address="Patiala" Email="Jitender@abc.com" PhoneNumber="897879" />

Now if you want your xml to be structured in very customized way , we can use for xml path . in this we have option to choose whether we want to show a particular column as an attribute or an element .

To display a column as an attribute add <‘@’> before alias name and to make an element just write the alias name .suppose you want to get every customer record with a Customer tag and first name with FisrtName attribute and last name with LastName Attribute.

SELECT CustomerId AS '@Id',

FirstName AS '@FirstName',

LastName AS '@LastName'

FROM dbo.Customer FOR XML PATH('Customer')

Resultant XML will be :

We can do any kind of manipulation in the select phrase and give it as an attribute of element of the xml :

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name'

FROM dbo.Customer FOR XML PATH('Customer')

Resultant XML will be :

To provide a root element of the table rows we need to use ROOT keyword

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name'

FROM dbo.Customer FOR XML PATH('Customer'), ROOT ('MyCustomers')

Resultant XML will be :

</MyCustomers>

Now suppose we need the Contact details as child element in the customer element , and within the Contact details we need three different attributes for address , email and phone number :

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name',

[Address] AS 'ContactDetails/@PostalAddress',

Email AS 'ContactDetails/@EmailAddress',

PhoneNumber AS 'ContactDetails/@PhoneNumber'

FROM dbo.Customer FOR XML PATH('Customer'), ROOT ('MyCustomers')

Resultant XML will be :

</Customer>

</Customer>

</Customer>

</Customer>

</Customer>

</MyCustomers>

Now if we want the address , phone and email should be elements within the customer tag :

SELECT CustomerId AS '@Id',

FirstName + ' ' + LastName AS '@Name',

[Address] AS 'ContactDetails/PostalAddress',

Email AS 'ContactDetails/EmailAddress',

PhoneNumber AS 'ContactDetails/PhoneNumber'

FROM dbo.Customer FOR XML PATH('Customer'), ROOT ('MyCustomers')

Resultant XML will be :

<PostalAddress>lucknow</PostalAddress>

<EmailAddress>priyanka@yahoo.com</EmailAddress>

</ContactDetails>

</Customer>

<PostalAddress>mumbai</PostalAddress>

<EmailAddress>manish@gmail.com</EmailAddress>

</ContactDetails>

</Customer>

<PostalAddress>Delhi</PostalAddress>

<EmailAddress>kanak@hotmail.com</EmailAddress>

</ContactDetails>

</Customer>

<PostalAddress>Bhopal</PostalAddress>

<EmailAddress>Rachita@abc.com</EmailAddress>

</ContactDetails>

</Customer>

<PostalAddress>Patiala</PostalAddress>

<EmailAddress>Jitender@abc.com</EmailAddress>

</ContactDetails>

</Customer>

</MyCustomers>

Joining multiple tables also work in same way u just need to understand the XML structure and apply elements or tags within the select clause .

Hope this will be helpful to start with the XML path . Actually you can do much more than this using this powerful SQL Feature :)

Let me know if you are facing any issue in implementing this in comment section of this blog .

Cheers

Priyanka

Comments

Anonymous
July 05, 2012
Very Helpful..
Anonymous
February 16, 2014
very helpful indeed! And best of all so simply written. Thank you.
Anonymous
March 12, 2015
It's really clear. Appreciate your sharing.
Anonymous
April 05, 2015
the concept is cleared in such a simple manner...highly appreciated..!
Anonymous
August 10, 2015
Clear and simple. Great explanation. Thx
Anonymous
November 16, 2015
Very well explained in simple way!!!

Freigeben über

FOR XML Path ( SQL Server )

For xml path gives you flexibility to generate xml from your database in the desired structure with less code .
Believe me once you learn the xml path you will love this feature .

Now if you want your xml to be structured in very customized way , we can use for xml path . in this we have option to choose whether we want to show a particular column as an attribute or an element .

Comments

Zusätzliche Ressourcen

Freigeben über

FOR XML Path ( SQL Server )

For xml path gives you flexibility to generate xml from your database in the desired structure with less code . Believe me once you learn the xml path you will love this feature .

Now if you want your xml to be structured in very customized way , we can use for xml path . in this we have option to choose whether we want to show a particular column as an attribute or an element .

Comments

Zusätzliche Ressourcen

For xml path gives you flexibility to generate xml from your database in the desired structure with less code .
Believe me once you learn the xml path you will love this feature .