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:
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:
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:
Output
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:
Output
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.
Output
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.
Output
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.
About Imran Nathani
 |
Sorry, no bio is available
This author has published 5 articles on DotNetSlackers. View other articles or the complete profile here.
|
You might also be interested in the following related blog posts
View and print Reporting Services Reports from Silverlight.
read more
Pass Tables to Stored Procedure / Table Valued Parameter
read more
Html Encoding Nuggets With ASP.NET MVC 2
read more
Dynamic in C# 4.0: Creating Wrappers with DynamicObject
read more
Migrated from Community Server to DasBlog
read more
Serialising Microsoft StreamInsight QueryTemplates
read more
Adding users to a TFS project when youre not on the domain
read more
Web Deployment Tool has gone RTW
read more
Postgresql - Day 2
read more
Migrating to Postgresql with my friend NHibernate
read more
|
|
Please login to rate or to leave a comment.