Dynamic XML from SQL Server

"Efficiency in simplicity", that's what XML is all about. 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. Though these features existed in the previous version of SQL Server they are highly enhanced in SQL Server 2005.

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:

SELECT * FROM customer FOR XML RAW
SELECT * FROM customer FOR XML AUTO
SELECT * FROM customer FOR XML AUTO, ROOT('customers')
SELECT * FROM customer FOR XML AUTO, ELEMENTS, 

ROOT('customers')
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:

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 AUTO mode 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 Auto query:

SELECT * FROM customer FOR XML RAW

Output

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

SELECT * FROM customer FOR XML AUTO

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:

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

Output

<customers>
<customer cid="1" 

cname="Name1" cadd="Address1" ctel="Tel1"/>
<customer cid="2" 

cname="Name2" cadd="Address2" ctel="Tel2"/>
<customer cid="3" 

cname="Name3" cadd="Address3" />
<customer cid="4" 

cname="Name4" /> 
</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.

SELECT * FROM customer FOR XML AUTO, ELEMENTS, 

ROOT('customers')

Output

<customers>
  <customer>
    <cid>1</cid>
    <cname>Name1</cname>
    <cadd>Address1</cadd>
    <ctel>Tel1</ctel>
  </customer>
  <customer>
    <cid>2</cid>
    <cname>Name2</cname>
    <cadd>Address2</cadd>
    <ctel>Tel2</ctel>
  </customer>
  <customer>
    <cid>3</cid>
    <cname>Name3</cname>
    <cadd>Address3</cadd>
  </customer>
  <customer>
    <cid>4</cid>
    <cname>Name4</cname>
  </customer>
</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.

SELECT * FROM customer FOR XML AUTO,ELEMENTS 

XSINIL,ROOT('customers')

Output

<customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <customer>
    <cid>1</cid>
    <cname>Name1</cname>
    <cadd>Address1</cadd>
    <ctel>Tel1</ctel>
  </customer>
  <customer>
    <cid>2</cid>
    <cname>Name2</cname>
    <cadd>Address2</cadd>
    <ctel>Tel2</ctel>
  </customer>
  <customer>
    <cid>3</cid>
    <cname>Name3</cname>
    <cadd>Address3</cadd>
    <ctel xsi:nil="true"/>
  </customer>
  <customer>
    <cid>4</cid>
    <cname>Name4</cname>
    <cadd xsi:nil="true"/>
    <ctel xsi:nil="true"/>
  </customer>
</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.

http://dotnetslackers.com/articles/xml/dynamic_xml_from_sql_server.aspx