Introduction

Microsoft recently released the second version of their SQLXML data provider. The SQLXML2 data provider is available from Microsoft as a free download (the URL is at the bottom of the article). It is intended for use with Microsoft SQL Server 2000 and MDAC 2.6+. The download actually installs two providers - one for regular ADO, and a second managed .NET provider, for use with ADO.NET development. These providers are intended to assist developers who are working with data from SQL Server when it is handled as XML. Support for automatically applying XSL transformations, executing XPath queries and XML templates, along with additional DiffGram support for ADO.NET is all included in this release. This article deals with the basics of using this provider, including installation, a basic overview of its architecture and a run-through of its basic features in both .NET and ASP.

Installation

Microsoft offers the SQLXML 2 provider as a free download from its MSDN website at http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/MSDN-FILES/027/001/602/msdncompositedoc.xml&WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK. After you've downloaded the Microsoft Installer setup file, installation is simply a matter of following several steps in the MSI wizard. Note that since an MSI file is used for distribution, you'll need to have the Microsoft Windows Installer 2 software installed on your development machine before you can setup SQLXML (the download URLs for MWI2 are available at the end of the article). Once installation has been completed, the SQLXMLOLEDB provider will have been installed, along with the .NET managed provider and the documentation.

Overview of the SQLXML Provider

The SQLXML Provider is in itself not a Data Provider, but is actually a layer on top of the SQLOLEDB provider. All calls to the database server are made through SQLOLEDB (or in the case of ADO.NET, the .NET managed provider for SQL).

As the name suggests, the SQLXML provider is intended specifically for dealing with XML data from a SQL data source. In SQL Server 2000, Microsoft introduced the "FOR XML " clause that could be used at the end of SELECT statements to return the data serialized as XML rather than SQL rows. For example, if you had a table called "Employees " with columns "FirstName ", "LastName " and "Salary " and executed a "SELECT * FROM Employees " query; you might receive SQL rows with the following data:

However, in SQL Server 2000, if the "FOR XML AUTO " clause were added to the original query (i.e. the query is now "SELECT * FROM Employees FOR XML AUTO "), then the following string would be returned:

<Employee FirstName="Werner" LastName="Burckard" Salary="56000"/>
<Employee FirstName="Philipa" LastName="Pletts" Salary="60000"/>
<Employee FirstName="Jaco" LastName="Van Der Walt" Salary="42000"/>
<Employee FirstName="Leon" LastName="Cilliers" Salary="72000"/>

This can be particularly useful when dealing with data that will be displayed, as XSL stylesheets can now be used without first having to serialize the data, or for use with web services, where data must again be serialized into XML.

The SQLXML provider can extend the functionality of the FOR XML clause by allowing you to specify where the serialization of rows to XML actually occurs. The serialization can occur on the server-side, where SQL Server does the serialization, or the conversion can occur on the client-side (i.e. the web server). This allows you to take the load incurred by this conversion off the database server and onto the middle tier (this is dealt with in the last section). It also paves the way for the SQLXML provider to be used with DBMSs other than SQL Server 2000, since if the serialization no longer takes place on the server machine, then the target DBMS does not need to support the FOR XML clause. Unfortunately in this release only the SQLOLEDB data provider is allowed to be specified. However, this future functionality could potentially be very useful, as you will be able to use the FOR XML clause in your data access without worrying about what might happen if you are forced to move from SQL Server to another DBMS.

Likewise XPath queries can also be executed against the database either on the server, or on the client-side. The template functionality is provided entirely on the client-side, with only the actual queries being executed on the server, so the template feature is one of the 'exclusive' features of the provider. The ability for XSL Transforms to be performed automatically is also included, which eliminates the need to have the XML COM objects installed, or in the case of .NET, to use the XML base classes to perform transformations.

In effect, the SQLXML provider is available to make the use of XML when interacting with a SQL Server database more unified, and although most of its functionality can be achieved using several different components and tools already, it packages a cohesive set of classes and methods that allow you to retrieve and manipulate SQL data using XML functionality without having to use any other objects.

Retrieving Data using SQLXML in ASP 3

Using the SQLXML provider to retrieve data is very similar to using any other OLE DB provider. However, because XML data, rather than data rows, will be returned, there are several subtle differences between using SQLXML and regular providers. The most important is the requirement that the ADODB Stream object is used to return the data. This object will be used to hold the returned XML data stream from the server. It is also essential that the ADODB Command object be used, as one or more properties must always be set before the server can be queried, and this would not be possible if the use of the Command object were omitted. Another detail to keep in mind when returning XML from the database is that there will be an increase in size in the physical amount of data that the DB server has to send to the middle tier.

The following example will execute a query against a fictitious database "HR" with a table called "Employees " that stores the names and salaries of a company's employees.

01 <!-- #INCLUDE FILE="adovbs.inc" -->
02 <%
03   Dim objStream
04   Dim objConnection
05   Dim objCommand
06   Dim strXML
07
08   Set objStream = Server.CreateObject("ADODB.Stream")
09   Set objConnection = Server.CreateObject("ADODB.Connection")
10   Set objCommand = Server.CreateObject("ADODB.Command")
11
12   objConnection.Open "provider=SQLXMLOLEDB.2.0;data 01 provider=SQLOLEDB;data source=
(local);initial catalog=HR;user id=sa;password=;"
13
14   objCommand.ActiveConnection = objConnection
15   objCommand.CommandText = "SELECT Employee.* FROM Employees AS Employee FOR XML AUTO"
16   objStream.Open
17   objCommand.Properties("Output Stream").Value = objStream
18   objCommand.Execute , , adExecuteStream
19   objStream.Position = 0
20   objStream.Charset = "utf-8"
21   strXML = objStream.ReadText(adReadAll)
22   objStream.Close
23   objConnection.Close
24
25   Set objStream = Nothing
26   Set objCommand = Nothing
27   Set objConnection = Nothing
28
29   Response.Write Server.HtmlEncode(strXML)
%>

The first line ensures that the ADO constants are available for use within the page. Lines 3 through 10 proceed to declare and instantiate the Stream , Connection and Command objects, which should be familiar. The opening of the connection on line12 is also the same as usual, bar the use of "SQLXMLOLEDB.2.0" as the "provider". Note that the "data provider" is still "SQLOLEDB", since the SQLXML provider is a layer on top of SQLOLEDB. In future you will be able to specify data providers other than SQLOLEDB. However, any attempt to do so currently will result in an error being thrown.

The command object's connection is assigned as per usual on line 14, as is the CommandText. An important point to note is the inclusion of the "FOR XML AUTO " clause here. This is obligatory in all calls using the SQLXML provider. The AS clause, which renames result set column names, is also used. Since XML is returned, the elements will be renamed.

The Stream object is then opened, and the following object sets a custom property of the SQLXML provider, which specifies the stream that the returned XML must be outputted to. The command is then executed in the regular fashion, but also take note of the inclusion of the adExecuteStream constant.

The following two lines ensure that the stream is read from the beginning, and the correct character set is used. The Stream object's ReadAll  method returns a string containing the returned XML. This is assigned to a variable, strXML. The objects are all closed (where applicable) and set to Nothing on lines 22 through 27, freeing them from memory. Finally the strXML string is outputted. The Server.HtmlEncode method is used so that the returned XML is actually visible when the page is loaded. The result might look something like the following:

<Employee FirstName="Martin" LastName="Hersalek" Salary="45000"/>
<Employee FirstName="Tirone" LastName="Nel" Salary="30000"/>
<Employee FirstName="Gary" LastName="Ronaldson" Salary="76000"/>
<Employee FirstName="Dave" LastName="Baxter" Salary="43000"/>

However, in this case, the SQLXML provider has done nothing that could not have been achieved without it by simply using the FOR XML clause. Obviously the data would not be displayed as raw XML, so the following extension to the above example uses a few of the custom SQLXML properties to automatically perform an XSL transform on the data to return HTML.

<!-- #INCLUDE FILE="adovbs.inc" -->
<%
  Dim objStream
  Dim objConnection
  Dim objCommand
  Dim strXML

  Set objStream = Server.CreateObject("ADODB.Stream")
  Set objConnection = Server.CreateObject("ADODB.Connection")
  Set objCommand = Server.CreateObject("ADODB.Command")

  objConnection.Open "provider=SQLXMLOLEDB.2.0;data provider=SQLOLEDB;data source=
(local);initial catalog=HR;user id=sa;password=;"
	
  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = "SELECT * FROM Employees FOR XML AUTO"
  objStream.Open
  objCommand.Properties("Output Stream").Value = objStream
  objCommand.Properties("xsl").Value = Server.MapPath("employees.xsl")
  objCommand.Properties("xml root").Value = "Employees"
  objCommand.Execute , , adExecuteStream
  objStream.Position = 0
  objStream.Charset = "utf-8"
  strXML = objStream.ReadText(adReadAll)
  objStream.Close
  objConnection.Close

  Set objStream = Nothing
  Set objCommand = Nothing
  Set objConnection = Nothing

  Response.Write strXML
%>

There are two additional lines in this code sample. The first sets the stylesheet that should be used for the transform. If the "xsl " property is specified, an XSL transform will be performed on the XML using the XSL stylesheet specified in this property. The next line sets the "xml root " property. Normally when SQL Server returns XML from a table, there is no root element - the rows are returned as XML elements, but there is no one "parent " element that encompasses them all. For an XML document to be valid, a root element should be specified. Setting the "xml root " property will result in a root element for the returned XML being created with the name assigned to the property. In the case above, a root element with the name "employees " will be created. The raw XML data returned by SQL Server would now look like the following after this root element is applied:

<Employees>
<Employee FirstName="Martin" LastName="Hersalek" Salary="45000"/>
<Employee FirstName="Tirone" LastName="Nel" Salary="30000"/>
<Employee FirstName="Gary" LastName="Ronaldson" Salary="76000"/>
<Employee FirstName="Dave" LastName="Baxter" Salary="43000"/>
</Employees>

The stylesheet specified in the "xsl " property can be any valid XSL stylesheet, and can display the returned data in any number of ways. The following is an example of the "employees.xsl " stylesheet, and will display the employee data in a table:

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <table border="0">
      <tr>
        <td>
          <b>First Name:</b>
        </td>
        <td>
          <b>Last Name:</b>
        </td>
        <td>
          <b>Salary: (US$)</b>
        </td>
      </tr>
      <xsl:for-each select="Employees/Employee">
      <tr>
        <td>
          <xsl:value-of select="@FirstName" />
        </td>
        <td>
          <xsl:value-of select="@LastName" />
        </td>
        <td>
          <xsl:value-of select="@Salary" />
        </td>
      </tr>
      </xsl:for-each>
    </table>
  </xsl:template>
</xsl:stylesheet>

The transform will be performed automatically, and thus the strXML string will contain the table HTML generated by the stylesheet. The strXML variable is again outputted using Response.Write , although since the HTML should be processed rather than displayed, the Server.HtmlEncode() method is not called. The page should look as follows:

Retrieving Data using SQLXML in ASP.NET

Just as using the SQLXML provider with ASP and ADO is very similar to using other providers, so too is using SQLXML in ASP.NET and ADO.NET when compared to "regular" ADO.NET. Microsoft has created three classes specifically for use with SQLXML - SqlXmlCommand , SqlXmlParameter and SqlXmlAdapter. These are direct equivalents of the standard ADO.NET Command , Parameter and Adapter classes. The only important point to take note of is that the SqlXml classes reside in their own .NET assembly (in the SQLXML folder under "Program Files "), and are located in the Microsoft.Data.SqlXml namespace, rather than the System.Data namespace.

The following examples will be demonstrated in Visual Studio.NET, although it is obviously possible to use the .NET SqlXml classes without it. The first step that needs to be taken before the classes can be used is that the project must include a "Reference " to the Microsoft.Data.SqlXml.dll.NET assembly where the classes are located. To do this, in the Solution Explorer , right click the "References " node, and choose "Add Reference " in the popup menu. As shown below, select "Microsoft.Data.SqlXml.dll " in the window that is displayed and click "Ok ".

Simply retrieving data is done in much the same way as through the regular ADO.NET data classes. However, instead of using a DataReader as the most basic class for simply reading through data, a Stream object is used to output the returned XML. To demonstrate acquiring and outputting the raw XML from a query using the SQLXML classes, place a HtmlLabel control onto a new Web Form and name it "divContent ". Place the following code in the Page_Load event handler in the codebehind:

Dim objStream As System.IO.Stream
Dim objStreamReader As System.IO.StreamReader
Dim objCommand As New Microsoft.Data.SqlXml.SqlXmlCommand("provider=SQLOLEDB;server=
(local);database=HR;user id=sa;password=;")
objCommand.CommandText = "SELECT Employee.* FROM Employees AS Employee FOR XML AUTO"
objStream = objCommand.ExecuteStream()

objStreamReader = New System.IO.StreamReader(objStream)
divContent.InnerHtml = Server.HtmlEncode(objStreamReader.ReadToEnd())

The first two lines create the Stream and StreamReader objects, which will be used to capture and output the returned XML. The third line creates the SqlXmlCommand class by providing the connection string as a parameter in the constructor. The following line defines the CommandText for the command, which will again return all the employees and an Employees table. Following this, the ExecuteStream() method of the SqlXmlCommand object is called. This is a method unique to the SqlXmlCommand , and is not a regular ADO.NET Command object method. It returns an instance of a Stream class, which is assigned to objStream. A StreamReader is then created from the Stream , and its contents finally outputted.

To have the XML automatically transformed using an XSL stylesheet, only two additional lines of code are needed. The Server.HtmlEncode() method is also no longer needed:

Dim objStream As System.IO.Stream
Dim objStreamReader As System.IO.StreamReader
Dim objCommand As New Microsoft.Data.SqlXml.SqlXmlCommand("provider=SQLOLEDB;server=
(local);database=HR;user id=sa;password=;")
objCommand.CommandText = "SELECT Employee.* FROM Employees AS Employee FOR XML AUTO"
objCommand.XslPath = Server.MapPath("employees.xsl")
objCommand.RootTag = "Employees"
objStream = objCommand.ExecuteStream()

objStreamReader = New System.IO.StreamReader(objStream)
divContent.InnerHtml = objStreamReader.ReadToEnd()

Firstly, the XslPath property is set to an appropriate stylesheet (the same one as in the ASP example), and the RootTag property is set to "Employees " so that the returned XML will be wrapped in an "Employees " tag. The result of this code should be identical to its ASP equivalent above.

Using XML Templates

XML Templates can be a useful tool, and allow for multiple SQL queries to be executed using only one command. XML Templates function by allowing an XML document to be created that features special tags where SQL queries must be executed. This allows a full XML document to be created featuring data from different tables using different SELECT statements, drastically simplifying the process of creating an XML document for display using an XSL stylesheet.

There is a slight difference in implementation between ASP and ASP.NET. Using the SqlXml managed classes in .NET, an XML file containing the XML Template is normally specified, whereas with the ADO classes and SQLXML provider in ASP the template is normally provided as a string, rather than a filename.

The following example will achieve the same results using either ASP or ASP.NET code. An XML template will specify three queries - one obtaining a list of employees, another obtaining the average salary of the employees and the third obtaining the number of employees. An XSL stylesheet will then be used to display this XML data in a suitable format.

Firstly, the XML template for the .NET sample will be stored in a file named "employeestempate.xml ". The contents of this file are as follows.

<EmployeesData xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <EmployeeStats>
    <TotalEmployees>
      <sql:query>
        SELECT COUNT(*) As TotalEmployees FROM Employees FOR XML RAW
      </sql:query>
    </TotalEmployees>
    <AverageSalary>
      <sql:query>
        SELECT AVG(Salary) As AverageSalary FROM Employees FOR XML RAW
      </sql:query>
    </AverageSalary>
  </EmployeeStats>
  <Employees>
    <sql:query>
      SELECT Employee.* FROM Employees AS Employee FOR XML AUTO
    </sql:query>
  </Employees>
</EmployeesData>

All data within a pair of <sql:query> tags is executed as a SQL query. This XML template includes three such pairs; so three queries will be executed. The results of each query are placed in the same position as the respective <sql:query> tags. The two queries using aggregate functions ( COUNT and AVG) include the FOR XML RAW clause, as opposed to FOR XML AUTO because the "AUTO " mode cannot be used with aggregate functions.

For the sample data that was used in the first exercise, the following XML would be returned if this XML template were executed:

<EmployeesData xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <EmployeeStats>
    <TotalEmployees>
      <row TotalEmployees="4"/>
    </TotalEmployees>
    <AverageSalary>
      <row AverageSalary="48500"/>
    </AverageSalary>
  </EmployeeStats>
  <Employees>
    <Employee FirstName="Martin" LastName="Hersalek" Salary="45000"/>
    <Employee FirstName="Tirone" LastName="Nel" Salary="30000"/>
    <Employee FirstName="Gary" LastName="Ronaldson" Salary="76000"/>
    <Employee FirstName="Dave" LastName="Baxter" Salary="43000"/>
  </Employees>
</EmployeesData>

This shows how the data from the SQL queries is placed within the original template. From this data an appropriate XSL stylesheet can be created:

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <table border="0">
      <tr>
        <td>
          <b>First Name:</b>
        </td>
        <td>
          <b>Last Name:</b>
        </td>
        <td>
          <b>Salary: (US$)</b>
        </td>
      </tr>
      <xsl:for-each select="EmployeesData/Employees/Employee">
      <tr>
        <td>
          <xsl:value-of select="@FirstName" />
        </td>
        <td>
          <xsl:value-of select="@LastName" />
        </td>
        <td>
          <xsl:value-of select="@Salary" />
        </td>
      </tr>
      </xsl:for-each>
    </table>
    Total Employees: <xsl:value-
ofselect="EmployeesData/EmployeeStats/TotalEmployees/row/@TotalEmployees" /><br />
    Average Salary: <xsl:value-of 
select="EmployeesData/EmployeeStats/AverageSalary/row/@AverageSalary" />
  </xsl:template>
</xsl:stylesheet>

This will list the employees in a table and display the statistics below the table. To demonstrate this in an ASP.NET page, create a new Web Form and add an HtmlLabel control named "divContent ". The following code must then be included in the Page_Load event handler:

Dim objStream As System.IO.Stream
Dim objStreamReader As System.IO.StreamReader
Dim objCommand As New Microsoft.Data.SqlXml.SqlXmlCommand("provider=SQLOLEDB;server=
(local);database=HR;user id=sa;password=;")
objCommand.CommandType = Microsoft.Data.SqlXml.SqlXmlCommandType.TemplateFile
objCommand.CommandText = Server.MapPath("employeestemplate.xml")
objCommand.XslPath = Server.MapPath("employeestemplate.xsl")
objStream = objCommand.ExecuteStream()

objStreamReader = New System.IO.StreamReader(objStream)
divContent.InnerHtml = objStreamReader.ReadToEnd()

This code is very similar to that used when a simple SQL query was executed. There are two changes to the code - firstly, the CommandType property is explicitly set, specifying that a template is going to be passed, and the CommandText property passes the path of the XML template file. The remainder of the code is the same, where the Stream object receives the returned HTML, and the StreamReader is used to display it. The results look similar to the following.

An identical result can be obtained using ASP as well. The code is also very similar to the previous ASP example where a single query was executed. In this sample, the same XML template as was used in the ASP.NET sample will be used. However, the entire template is passed as a string, rather than including the template in a file and passing the filename.

<!-- #INCLUDE FILE="adovbs.inc" -->
<%
  Dim objStream
  Dim objConnection
  Dim objCommand
  Dim strXML

  Set objStream = Server.CreateObject("ADODB.Stream")
  Set objConnection = Server.CreateObject("ADODB.Connection")
  Set objCommand = Server.CreateObject("ADODB.Command")
  
  objConnection.Open "provider=SQLXMLOLEDB.2.0;data provider=SQLOLEDB;data source=
(local);initial catalog=HR;user id=sa;password=;"

  objCommand.ActiveConnection = objConnection
  objCommand.CommandText = "<EmployeesData xmlns:sql='urn:schemas-microsoft-com:xml-
sql'><EmployeeStats><TotalEmployees><sql:query>SELECT COUNT(*) As 
TotalEmployees FROM Employees FOR XML 
RAW</sql:query></TotalEmployees><AverageSalary><sql:query>SELECT AVG
(Salary) As AverageSalary FROM Employees FOR XML 
RAW</sql:query></AverageSalary></EmployeeStats><Employees><sql:query>S
ELECT Employee.* FROM Employees AS Employee FOR XML 
AUTO</sql:query></Employees></EmployeesData>"
  objStream.Open
  objCommand.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
  objCommand.Properties("Output Stream").Value = objStream
  objCommand.Properties("xsl").Value = Server.MapPath("employeestemplate.xsl")
  objCommand.Execute , , adExecuteStream
  objStream.Position = 0
  objStream.Charset = "utf-8"
  strXML = objStream.ReadText(adReadAll)
  objStream.Close
  objConnection.Close

  Set objStream = Nothing
  Set objCommand = Nothing
  Set objConnection = Nothing

  Response.Write strXML
%>

Take note that not only is the CommandText property different from the original sample (it now specifies an XML template), but the Dialect property has also been set. This is a SQLXML requirement when templates are being used. However, the remainder of the code is essentially the same (the stylesheet has been changed to the one for this XML template), and will render identical results to the ASP.NET XML template sample.

Client-side XML Processing

One of the more useful features of the SQLXML provider is the ability for the data records to be converted to XML on the middle-tier or web server. This allows unnecessary load to be taken off the database server and put onto the application server. This is possible in both ASP and ASP.NET, and is equally simple to implement in both environments. In ASP, the ClientSideXml property is set on the Command object as follows:

objCommand.Properties("ClientSideXml") = True

In ASP.NET, the SqlXmlCommand object has its own ClientSideXml property which can be set like so:

objCommand.ClientSideXml = True

In both cases this property would obviously be set before the Execute method is called. There is however one limitation on this functionality - when the "FOR XML AUTO " clause is used, the statement is always executed on the server. Only the "EXPLICIT ", "RAW " and "NESTED " "FOR XML " modes will result in the XML conversion taking place on the client-side if the ClientSideXml property is true. To ensure that the client is indeed doing the conversion, and not the server, the SQL Server Profiler can be used to ensure that the "FOR XML " clause is not being sent to the server.

Conclusion

The SQLXML2 release provides a marked advantage over SQL Server's native XML processing capabilities and is a very worthwhile tool for any XML developer working with SQL Server. The automatic XSL transforms capability can be very useful, and the XML Templates functionality is a great tool that would otherwise not be available. The ability to delegate where the XML processing takes place can also potentially be used to great advantage. This article has covered a few, but not all of the SQLXML2 provider's capabilities, and you may also find the XPath and .NET datagrams support offered in this release interesting.

External resources:

http://www.sqlxml.org/?WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK - SQLXML Resource Website

http://msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/MSDN-FILES/027/001/602/msdncompositedoc.xml&WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK - Microsoft SQLXML2 Release Download

http://www.microsoft.com/sql/techinfo/xml/default.asp?WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK - Microsoft SQL Server XML Homepage

nntp://microsoft.public.sqlserver.xml/?WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK - Microsoft SQL Server XML Newsgroup

http://www.microsoft.com/downloads/release.asp?releaseid=32832&NewList=1&WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK - Microsoft Windows Installer 2 for Windows NT4/2000

http://www.microsoft.com/downloads/release.asp?ReleaseID=32831&WROXEMPTOKEN=15494Z54pLBDtcysW8oXeAkSCK - Microsoft Windows Installer 2 for Windows 95/98/Me