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 article will let you know how to export the data from SQL Server 2005 to XML using different modes.

–Test Data
SELECT * FROM Customers WITH (NOLOCK)

Result is
CustomerID CustomerName ActiveStatus
———– —————————— ————
100 John 0
200 Kate 1
300 Julia 1
400 Maddy 0
(4 row(s) affected)

Example for XML PATH, ROOT()
SELECT * FROM Customers (NOLOCK)
FOR XML PATH, ROOT(‘root’)

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>

Example 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 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>