The purpose of this article is to explain how to use XML in SQL Server 2005 and generate different kind of XML files to database. SQL Server is such a popular database. Since Microsoft has added many other features to SQL Server 2005, it is worth to learning XML features in SQL server.

Introduction:

Most business applications store data in some kind of data store, which is usually a relational database. To that end, SQL Server is one of Microsoft’s flagship products. With increasing the use of XML in business applications, Microsoft found it is necessary to incorporate strong support for XML in their database engine as well, with SQL Server 2000 being possibly the first version where this XML integration was clearly visible.


Prerequisites:

1. SQL Server 2005

Note: In this article I like to explore how we can use the XML in SELECT statement and generate different kind of XML files. To do this we have to realize what are the keywords are within SQL Server 2005.

1. FOR XML Clause

To fetch FOR XML clause SQL Server data in XML format, you need to use the FOR XML clause with the SELECT statement. The FOR XML clause has four modes that allow you to return the XML results in different formats.

AUTO:

The AUTO mode returns the results of the SELECT query as XML fragments. By default it returns the data as XML elements. The name of the XML element is the same as the table name, and column values are return as XML attributes. You have the option to return all the columns as elements instead of attributes.

RAW: The RAW mode returns the results as a <row> element. The column values are return as XML attributes.

PATH: The PATH mode allows you to define the nesting of the returned XML by using simple XPath syntax.

EXPLICIT:  The EXPLICIT mode defines a schema for the returned results explicitly in the SELECT query.

Before apply keywords we need design a Database for demonstration.




The created database name is “HouseRental” with “HouseOwnerInfo” table. There are eight columns in the “HouseOwnerInfo.
Now we can apply different XML SELECT Clause and able to see output.


1. SELECT * FROM HouseOwnerInfo FOR XML AUTO



We just write a select query with default XML clause would get output like above. Look at the XML output, all columns are come as Attribute <HouseOwnerInfo  …   /> Tag.

2. SELECT * FROM HouseOwnerInfo FOR XML AUTO,ELEMENTS



Look above output all columns are come out as Child element to HouseOwnerInfo tag, because when we are using the ELEMENTS keyword with FOR XML AUTO it will understand and generate table columns to child Elements with Table Name.

3. SELECT * FROM HouseOwnerInfo FOR XML RAW
      
Output:


Here look at the output, it come tag as “Row”, when we are using the RAW keyword, it will replace table name to “row”. But we can get output like in second step.
    
4. SELECT * FROM HouseOwnerInfo FOR XML RAW('HouseOwnerInfo')


 
Look at the above output, we use the RAW keyword and with that we pass the argument to the Tag name RAW('HouseOwnerInfo').This time we got tag name not table name instead of “row”

5. SELECT * FROM HouseOwnerInfo FOR XML AUTO, XMLSCHEMA



Look at the above output, we generate XMLSchema for table with complete information by above SELECT caluse. For that we used  “ .. FOR XML AUTO, XMLSCHEMA”


6.     SELECT LTRIM(RTRIM(OwnerID)) "@OwnerID",FName,LName,DateOfPurchase,PlanType,HPhone,MPhone,RStatus
 FROM HouseOwnerInfo FOR XML PATH('HouseOwnerInfo')

It is quite intereting, because say we have desgined our table with primary key column and we need to keep that column value as an Attribute in the tablename. So how we can generate XML file, for this we can use PATH keyword like above.

7.

SELECT
1 AS Tag,
NULL AS Parent,
OwnerID AS [HouseOwnerInfo!1!OwnerID],
FName AS [HouseOwnerInfo!1!FName!element],
LName AS [HouseOwnerInfo!1!LName!element]
FROM HouseOwnerInfo
FOR XML EXPLICIT


This is little complex mode, because we need to supply all information built XML file from the table.The benefits of using this mode, here we can define output  structure of the XML file completelly.

8.


Sometimes we need build XML file to specify table informations under the Root tag like”HouseOwnerInfos”.

This time we can use the ROOT(‘…’)


Finally generate complex XML File


Look at the above output, here we can use the all feaures which we learnt previous.

Conclusion:

In this article you learned how you can use the XML features within SQL SERVER 2005 database. And also how to generate XML files  useing XML Support keywords.

Author:

- B.Sc. degree in Computer Science.
- 4+ years experience in C#.net,VB.net adn JAVA.
- Obsessed in OOP style design and programming.
- Designing and developing Network security tools.
- Designing and developing a client/server application for sharing files among users in a
  way other than FTP protocol.
- Designing and implementing GSM gateway applications and bulk messaging.
- Windows Mobile and Symbian Programming
- Having knowledge with ERP solutions

The summary of my skills:
C#, VB.Net#,ASP.net, VC++, Java, WPF,WCF, SilverLight,Oracle,PL/SQL, SQL Server, MS Access
My Web Blogs
http://ravesoft.blogspot.com
http://netcodesnippet.blogspot.com/