Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Convert Table Data to XML Format using XML PATH, XML AUTO or XML RAW

Oct 20, 2014
Introduction:

Here I will explain how to convert table data to xml format in
sql server or get table data in xml format in sql server. We can convert sql server table data in xml format by using XML AUTO or XML PATH or XML RAW.
Description:
In previous articles I explained SQL Server Get Data in XML format  group by columns, Reading XML data file using SQL query, Pass XML file as parameter from C# to SQL stored procedure, cursor example in sql server and many articles relating to SQL server. Now I will explain how to convert table data to xml format in sql server or get table data in xml format in sql server.

We can convert table data in xml format in different ways

Using XML Path Statement

If you want to use XML Path statement to convert table data to xml format we need to write the query like as shown below

 select * from sampletable  FOR XML PATH('SubRoot'), ROOT('RootName')
Example


DECLARE @T TABLE
(
ID INT,
Name VARCHAR(30)
)
INSERT INTO @T VALUES
(1, 'Functional Brochures'),
(1,   'Fliers'),
(2, 'Data Sheets'),
(2, 'Catalogs'),
(5, 'User Guides')
SELECT * FROM @T FOR XML PATH('Subject'), ROOT('SubjectDetails')
OUTPUT

 SQL Server Convert Table Data to XML Format using XML PATH
Using XML RAW Statement

If you want to use XML RAW statement to convert table data to xml format we need to write the query like as shown below

 select * from @T FOR XML RAW('Subject'), ROOT('SubjectDetails')
Example

 DECLARE @T TABLE
(
ID INT,
Name VARCHAR(30)
)
INSERT INTO @T VALUES
(1, 'Functional Brochures'),
(1,   'Fliers'),
(2, 'Data Sheets'),
(2, 'Catalogs'),
(5, 'User Guides')
select * from @T FOR XML RAW('Subject'), ROOT('SubjectDetails')
OUTPUT

 SQL Server Convert Table Data to XML Format using XML RAW
Using XML AUTO Statement

If you want to use XML AUTO statement to convert table data to xml format we need to write the query like as shown below

select * from @T FOR XML AUTO, ROOT('SubjectDetails')
Example


DECLARE @T TABLE
(
ID INT,
Name VARCHAR(30)
)
INSERT INTO @T VALUES
(1, 'Functional Brochures'),
(1,   'Fliers'),
(2, 'Data Sheets'),
(2, 'Catalogs'),
(5, 'User Guides')
select * from @T FOR XML AUTO, ROOT('SubjectDetails')
OUTPUT

 

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

4 comments :

Unknown said...

Not getting complete data from the table. only getting mentioned numbers.

select len((
SELECT * FROM [tblDocument] FOR XML PATH('Document'), ROOT('Documents')
)) --1018504

select len((
select * from [tblDocument] FOR XML RAW('Document'), ROOT('Documents')
)) --802396

select len((
select * from [tblDocument] FOR XML AUTO, ROOT('Documents')
)) --814402

Unknown said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
john said...

The SQL syntax is specific for SQL server right?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.