Tuesday, March 16, 2010

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>
*/

1 comment:

  1. This is such a convenient way to refer to the most essential parts of sql programming. This blog has saved my time from reading through tedious texts in books. Thank you for summarizing each topic so simply.

    ReplyDelete

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...