Introduction

SQL 2000 Server provided T-SQL language extensions to operate bi-directionally with relational and XML sources. It also provided two system stored procedures, sp_XML_preparedocument and sp_XML_removedocument, that assist the XML to Relational transformation. This support for returning XML data from relational data using the For XML clause is continued in SQL Server 2005 and SQL Server 2008 although the support for XML is lot more extensive. The shape of the data returned by the For XML clause is further modified by choosing the following modes, raw, auto, explicit, or path. As a preparation for this article we will be creating an XML document starting from the PrincetonTemp table used in a previous article, Binding MS Chart Control to LINQ Data Source Control, on this site.

Creating an XML document from an SQL Table

Open the SQL Server Management and create a new query [SELECT * from PrincetonTemp for XML auto]. You can use the For XML Auto clause to create a XML document (actually what you create is a fragment – a root-less XML without a processing directive) as shown in Figure 01.

Figure 01: For XML Auto clause of a SELECT statement

The result shown in a table has essentially two columns with the second column containing the document fragment shown in the next listing.

Listing 01:

<PrincetonTemp Id="1" Month="Jan       " Temperature="4.000000000000000e+001" RecordHigh="6.000000000000000e+001"/>
<PrincetonTemp Id="2" Month="Feb       " Temperature="3.200000000000000e+001" RecordHigh="5.000000000000000e+001"/>
<PrincetonTemp Id="3"Month="Mar        " Temperature="4.300000000000000e+001" RecordHigh="6.500000000000000e+001"/>
<PrincetonTemp Id="4" Month="Apr       " Temperature="5.000000000000000e+001" RecordHigh="7.000000000000000e+001"/>
<PrincetonTemp Id="5" Month="May       " Temperature="5.300000000000000e+001" RecordHigh="7.400000000000000e+001"/>
<PrincetonTemp Id="6" Month="Jun       " Temperature="6.000000000000000e+001" RecordHigh="7.800000000000000e+001"/>
<PrincetonTemp Id="7" Month="Jul       " Temperature="6.800000000000000e+001" RecordHigh="7.000000000000000e+001"/>
<PrincetonTemp Id="8" Month="Aug       " Temperature="7.100000000000000e+001" RecordHigh="7.000000000000000e+001"/>
<PrincetonTemp Id="9" Month="Sep       " Temperature="6.000000000000000e+001" RecordHigh="8.200000000000000e+001"/>
<PrincetonTemp Id="10" Month="Oct      " Temperature="5.500000000000000e+001" RecordHigh="6.700000000000000e+001"/>
<PrincetonTemp Id="11" Month="Nov      " Temperature="4.500000000000000e+001" RecordHigh="5.500000000000000e+001"/>
<PrincetonTemp Id="12" Month="Dec      " Temperature="4.000000000000000e+001" RecordHigh="6.200000000000000e+001"/>

This result is attribute-centric as each row of data corresponds to a row in the relational table with each column represented as an XML attribute.

The same data can be extracted in an element centric manner by using the directive elements in the SELECT statement as shown in the next figure.

Figure 02: For XML auto, Elements clause of a Select statement

This would still give us an XML fragment but now it is displayed with element nodes as shown in the next listing (only two nodes 1 and 12 are shown).

Listing 02:

<PrincetonTemp><Id>1</Id><Month>Jan </Month><Temperature>4.000000000000000e+001</Temperature>
<RecordHigh>6.000000000000000e+001</RecordHigh> </PrincetonTemp>
 ...
  <PrincetonTemp><Id>12</Id><Month>Dec </Month><Temperature>4.000000000000000e+001</Temperature>
<RecordHigh>6.200000000000000e+001 </RecordHigh></PrincetonTemp>

To make a clear distinction between the results returned by the two select statements the first row of data is shown in blue. This has returned elements and not attributes. As you can see the returned XML still lacks a root element as well as the XML processing directive.

To continue with displaying this data in MS Chart Save Listing 2 as PrincetonXMLDOC.xml to a location of your choice.

Create a Framework 3.5 Web Site project

Let us create a web site project and display the chart on the Default.aspx page. Open Visual Studio 2008 from its shortcut on the desktop. Click File  New | Web Site…|(or Shift+Alt+N) to open the New Web Site window. Change the default name of the site to a name of your choice (herein Chart_XMLWeb) as shown. Make sure you are creating a .NET Framework 3.5 web site as shown here.

Figure 03: New Framework 3.5 Web Site Project

Click on APP_Data folder in the solution explorer as shown in the next figure and click on Add Existing Item… menu item.

Figure 04: Add an existing item to the web site folder

In the interactive window that gets displayed browse to the location where you saved the PrincetonXMLDOC.xml file and click Add button. This will add the XML file to the ADD_Data folder of the web site project.

Double click PrincetonXMLDOC.xml in the web site project folder to display and verify its contents as shown in the next figure. Only nodes 1 and 12 are shown expanded. As mentioned previously this is an XML fragment.

Figure 05: Imported PrincetonXMLDOC.xml

Modify this document by adding the <root/> as well as the XML processing instruction as shown in the next figure. Build the project.

Figure 06: Modified PrincetonXMLDOX.xml (valid XML document)