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:

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