SQL Server - Data Export to XML

One of my friend requested me to explain how many ways are there to export the data from SQL Server 2005 into an XML file. Well, this artcle will let you know how to export the data from SQL Server 2005 to XML using different modes.

SELECT * FROM Customers (NOLOCK)

/* Result is

CustomerID CustomerName ActiveStatus

----------- ------------------------------ ------------

100 John 0

200 Kate 1

300 Julia 1

400 Maddy 0

(4 row(s) affected)

*/

SELECT * FROM Customers (NOLOCK)

FOR XML PATH, ROOT('root')

(Please note that I have placed "`" symbol after "<" and ">" in the results to avoid execution in the web) 

/* Result is

<`root`>

<`row`>

<`CustomerID>100<`/CustomerID>

<`CustomerName>John<`/CustomerName>

<`ActiveStatus>0<`/ActiveStatus>

<`/row><`row`>

<`CustomerID>200<`/CustomerID>

<`CustomerName>Kate<`/CustomerName>

<`ActiveStatus>1<`/ActiveStatus>

<`/row><`row>

<`CustomerID>300<`/CustomerID>

<`CustomerName>Julia<`/CustomerName>

<`ActiveStatus>1<`/ActiveStatus>

<`/row><`row>

<`CustomerID>400<`/CustomerID>

<`CustomerName>Maddy<`/CustomerName>

<`ActiveStatus>0<`/ActiveStatus>

<`/row>

<`/root>*/

/**************************************/

/** EXAMPLES FOR AUTO MODE **/

/**************************************/

In order to generate simple hierarchies we can use AUTO mode. Since the result will be in form of nested elements, it doesn't provide much control over the shape of the XML whereas EXPLICIT and PATH modes provide better control and shape of the XML.

SELECT * FROM Customers FOR XML AUTO, TYPE

/* Result is

<`Customers CustomerID="100" CustomerName="John" ActiveStatus="0" /`>

<`Customers CustomerID="200" CustomerName="Kate" ActiveStatus="1" /`>

<`Customers CustomerID="300" CustomerName="Julia" ActiveStatus="1" /`>

<`Customers CustomerID="400" CustomerName="Maddy" ActiveStatus="0" /`>

*/

Using Variables

DECLARE @cust XML;

SET @cust = (SELECT * FROM Customers FOR XML AUTO, TYPE)

SELECT @cust

/* Result is

<`Customers CustomerID="100" CustomerName="John" ActiveStatus="0" /`>

<`Customers CustomerID="200" CustomerName="Kate" ActiveStatus="1" /`>

<`Customers CustomerID="300" CustomerName="Julia" ActiveStatus="1" /`>

<`Customers CustomerID="400" CustomerName="Maddy" ActiveStatus="0" /`>

*/

XML Data is into another table
CREATE TABLE Test1(i int, x XML)

INSERT INTO Test1 SELECT 1, (SELECT * FROM Customers FOR XML AUTO, TYPE)
SELECT * FROM Test1

/* Result is

<'Customers CustomerID="100" CustomerName="John" ActiveStatus="0" /`>

<'Customers CustomerID="200" CustomerName="Kate" ActiveStatus="1" /`>

<'Customers CustomerID="300" CustomerName="Julia" ActiveStatus="1" /`>

<'Customers CustomerID="400" CustomerName="Maddy" ActiveStatus="0" /`>

*/

/************************************/

/** EXAMPLE FOR RAW MODE **/

/************************************/
Each row of the result set from the query will be converted into element. The column from the result set will be mapped to the attribute of the row element.

SELECT *

FROM Customers

FOR XML RAW, ELEMENTS

/* Result is

<`row>

<`CustomerID>100<`/CustomerID>

<`CustomerName>John<`/CustomerName>

<`ActiveStatus>0<`/ActiveStatus>

<`/row><`row>

<`CustomerID>200<`/CustomerID>

<`CustomerName>Kate<`/CustomerName>

<`ActiveStatus>1<`/ActiveStatus>

<`/row><`row>

<`CustomerID>300<`/CustomerID>

<`CustomerName>Julia<`/CustomerName>

<`ActiveStatus>1<`/ActiveStatus>

<`/row><`row>

<`CustomerID>400<`/CustomerID>

<`CustomerName>Maddy<`/CustomerName>

<`ActiveStatus>0<`/ActiveStatus>

<`/row>

*/

You can rename the element by using optional argument in RAW Mode.
SELECT * FROM Customers

FOR XML RAW ('CustomerDetails'), ELEMENTS

/* Result is

<`CustomerDetails>

<`CustomerID>100<`/CustomerID>

<`CustomerName>John<`/CustomerName>

<`ActiveStatus>0<`/ActiveStatus>

<`/CustomerDetails><`CustomerDetails>

<`CustomerID>200<`/CustomerID>

<`CustomerName>Kate<`/CustomerName>

<`ActiveStatus>1<`/ActiveStatus>

<`/CustomerDetails><`CustomerDetails>

<`CustomerID>300<`/CustomerID>

<`CustomerName>Julia<`/CustomerName>

<`ActiveStatus>1<`/ActiveStatus>

<`/CustomerDetails><`CustomerDetails>

<`CustomerID>400<`/CustomerID>

<`CustomerName>Maddy<`/CustomerName>

<`ActiveStatus>0<`/ActiveStatus>

<`/CustomerDetails>

*/

/*****************************************/

/** EXAMPLE FOR EXPLICIT MODE **/

/*****************************************/

This mode is more recommended one when compare with RAW and AUTO modes. It is because of the control over the shape of the XML.

For more details refer to :
http://msdn.microsoft.com/en-us/library/ms189068.aspx

CREATE VIEW DataExport AS

SELECT

1 AS Tag,

NULL AS Parent,

'CustomerID' AS [data!1!identifier],

NULL AS [record!2!CustomerID!element] ,

NULL AS [record!2!CustomerName!element],

NULL AS [record!2!ActiveStatus!element]

UNION ALL

SELECT

2 AS Tag,

1 AS Parent,

'CustomerID' AS [data!1!identifier],

CustomerID AS [record!2!CustomerID!element] ,

CustomerName AS [record!2!CustomerName!element],

ActiveStatus AS [record!2!ActiveStatus!element]

FROM Customers SELECT * FROM DataExport

FOR XML EXPLICIT

/* Result is

<`data identifier="CustomerID">
<`record>
<`CustomerID>100<`/CustomerID>
<`CustomerName>John<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/record>
<`record>
<`CustomerID>200<`/CustomerID>
<`CustomerName>Kate<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/record>
<`record>
<`CustomerID>300<`/CustomerID>
<`CustomerName>Julia<`/CustomerName>
<`ActiveStatus>1<`/ActiveStatus>
<`/record>
<`record>
<`CustomerID>400<`/CustomerID>
<`CustomerName>Maddy<`/CustomerName>
<`ActiveStatus>0<`/ActiveStatus>
<`/record>
<`/data>
*/