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

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

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

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 Email