Generating XML using SQL script in MS SQL Server 2005/2008/2012

For providing solution for complete offline data entry application. The data will require to synch with the server via mail. On brainstorming on multiple option we came across solution of desktop .net application, which would be using Master XML files for populating data.

XML is great for information exchange because of its simple flat file structure and user defined tags. For any application to interact with another either an complex marshalling code would be required or a simple implementation of XML would suffice. MS SQL gives us the advantage of generating dynamic XML in our data queries itself. In this article we will see some of the common used SQL XML queries.

Introduction

In this article, we will see how to use normal relational data and prepare dynamic XML used for XML based applications.

Our Data Table

Let us consider the famous customer table which looks usually looks as illustrated below:

cid cname cadd ctel
1 Name1 Address1 Tel1
2 Name2 Address2 Tel2
3 Name3 Address3 NULL
4 Name4 NULL NULL

Available XML queries in SQL

Below are common used XML queries that we are going to discuss in this article:

1.SELECT FROM customer FOR XML RAW
2.SELECT FROM customer FOR XML AUTO
3.SELECT FROM customer FOR XML AUTO, ROOT('customers')
4.SELECT FROM customer FOR XML AUTO, ELEMENTS, ROOT('customers')
5.SELECT FROM customer FOR XML AUTO, ELEMENTS XSINIL, ROOT('customers')

The FOR XML Clause

The For XML clause does most of the work for us and it can be used in various ways. The basic syntax of the query is:

1.SELECT FROM customer FOR XML [output mode], [display keyword]

RAW and AUTO output modes

These are two major used output modes which can further be customized by the display keywords to achieve most structures of XML derived from a particular table. The RAW mode takes each element as a row element and all the column values are taken as the attribute of that row element. On the other hand the AUTOmode outputs each element as the table name and the column values as attributes of these elements. Below is an example of the XML Raw and For XML Autoquery:

1.SELECT FROM customer FOR XML RAW

Output

1.<row cid="1" cname="Name1" cadd="Address1" ctel="Tel1"/>
2.<row cid="2" cname="Name2" cadd="Address2" ctel="Tel2"/>
3.<row cid="3" cname="Name3" cadd="Address3" />
4.<row cid="4" cname="Name4" />

Note: NULL values are omitted in both cases.

Display Keywords

There are many display keywords available and to cover all of them is out of the scope of this article, however I will cover the most used keywords.

1) ROOT

As we all know well formed XML documents must have a root node and subsequent instances of data should be under that one root node. To achieve this in our example we rewrite our query as:

1.SELECT FROM customer FOR XML AUTO, ROOT('customers')

Output

01.<customers>
02.<customer cid="1"
03.
04.cname="Name1" cadd="Address1" ctel="Tel1"/>
05.<customer cid="2"
06.
07.cname="Name2" cadd="Address2" ctel="Tel2"/>
08.<customer cid="3"
09.
10.cname="Name3" cadd="Address3" />
11.<customer cid="4" cname="Name4" />
12.</customers>

2) ELEMENTS

As we have seen above, the columns are simply attributes and not actually the node data. To get the data we use the ELEMENTS display keyword. This would break each cell in the relational table to an individual node.

1.SELECT FROM customer FOR XML AUTO, ELEMENTS, ROOT('customers')

Output

01.<customers>
02.<customer>
03.<cid>1</cid>
04.<cname>Name1</cname>
05.<cadd>Address1</cadd>
06.<ctel>Tel1</ctel>
07.</customer>
08.<customer>
09.<cid>2</cid>
10.<cname>Name2</cname>
11.<cadd>Address2</cadd>
12.<ctel>Tel2</ctel>
13.</customer>
14.<customer>
15.<cid>3</cid>
16.<cname>Name3</cname>
17.<cadd>Address3</cadd>
18.</customer>
19.<customer>
20.<cid>4</cid>
21.<cname>Name4</cname>
22.</customer>
23.</customers>

3) ELEMENTS XSINIL

All thru out we see that the NULL values if the table are omitted and not accounted for. In many cases even the NULL values are required and important, hence we use the ELEMENTS XSINIL keyword to account for the same.

1.SELECT FROM customer FOR XML AUTO,ELEMENTS XSINIL,ROOT('customers')

Output

01.<customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
02.<customer>
03.<cid>1</cid>
04.<cname>Name1</cname>
05.<cadd>Address1</cadd>
06.<ctel>Tel1</ctel>
07.</customer>
08.<customer>
09.<cid>2</cid>
10.<cname>Name2</cname>
11.<cadd>Address2</cadd>
12.<ctel>Tel2</ctel>
13.</customer>
14.<customer>
15.<cid>3</cid>
16.<cname>Name3</cname>
17.<cadd>Address3</cadd>
18.<ctel xsi:nil="true"/>
19.</customer>
20.<customer>
21.<cid>4</cid>
22.<cname>Name4</cname>
23.<cadd xsi:nil="true"/>
24.<ctel xsi:nil="true"/>
25.</customer>
26.</customers>

Summary

In this article you have seen how to generate XML from a relational table using SQL server 2005 and with the introduction of the xml datatype in SQL we are able to store and retrieve XML data easily. While the FOR XML clause gives us the power to tailor the XML to our needs.

Advertisements