Using the SQLXML Managed Classes

By now you probably know that SQL Server 2005 (formerly code-named
“Yukon”) will include tight integration between SQL Server and the .NET
Framework, as well as new XML features. But did you know that Microsoft
regularly releases a library of .NET and XML add-ons that works with SQL
Server 2000? Called SQLXML, this library can help you with quite a few
XML-related tasks. In this article, I’ll give you a quick look at the
bridges that SQLXML provides between SQL Server 2000 and the .NET Framework.

Getting SQLXML

SQLXML is a free download from Microsoft that is updated quite frequently. As
I write this, the current version is SQLXML
3.0 SP2. You should always be able to find a link to the most recent version
at the SQLXML
and XML Mapping Technologies developer center on MSDN.

After you’ve downloaded SQLXML, installation is simply a matter of running
the setup file. You’ll need to have a recent release of MDAC (version 2.6 or
later) installed, and you need the .NET Framework installed to use the managed
classes. If you want to use the Web Services functionality built into SQLXML,
you also need to have the SOAP
Toolkit installed. As the SOAP Toolkit is deprecated and scheduled to be
retired this year, though, I wouldn’t recommend going down that particular
path.

SQLXML also depends on having the MSXML parser installed on your machine, but
the SQLXML setup will install MSXML if it’s not there already, so don’t worry
about it.

The Big Picture

Before I drill down on the managed classes, I should let you know what else
you get in this package. Remember, SQL Server 2000 already has a base level of
XML functionality with the FOR XML clause in the SELECT statement, which allows
you to return query results as XML. SQLXML adds these features to the mix:

• An ISAPI extension that provides the ability to query SQL Server data
  through HTTP requests.
• Client-side FOR XML processing to ease the load on the server for XML query
  output.
• XML view creation via annotated XSD schemas.
• XPath query support for both HTTP and XML views.
• Updategram support for modifying data by sending XML messages containing
  before and after data.
• A COM object that provides bulk loading of XML data.
• The SQLXMLOLEDB provider, which exposes some SQLXML functionality to
  ADO.
• The SQLXML Managed Classes to expose SQLXML functionality to .NET
  applications.
• Web services support for directly exposing SQL Server data via SOAP.

All in all, there’s quite a bit of code here. Even if you’re familiar with
all of the relevant XML specifications, plan on spending a few days exploring if
you decide this library might be right for your application.

The SQLXML Managed Classes

The SQLXML Managed Classes consist of three classes in a single .NET
library:

• SqlXmlCommand, which allows you to send various XML-flavored
  commands to SQL Server.
• SqlXmlParameter, which provides parameter support for the
  SqlXmlCommand class.
• SqlXmlAdapter, which lets you easily hook up SQL Server XML
  data to the standard ADO.NET DataSet object.

A couple of examples of using these objects will give you a sense of the
tasks that the SQLXML Managed Classes are suited for.

Filling a DataSet From an XSD Schema

An XSD schema lets you describe tables, columns, and relations in a standard
XML format. If you’ve ever built a strongly-typed DataSet with .NET, you’ve used
an XSD schema behind the scenes. You can also use an XSD schema to define a
subset of data from a SQL Server database; roughly, this is equivalent to
defining a view with XML. For example, here’s an XSD schema that specifies three
fields from the Northwind Customers table:

<?xml version="1.0" encoding="utf-8" ?> 
<xsd:schema xmlns_xsd="http://www.w3.org/2001/XMLSchema"
            xmlns_sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Cust" sql_relation="Customers" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"  
                     sql_field="CompanyName" 
                     type="xsd:string" /> 
        <xsd:element name="LName"  
                     sql_field="ContactName"  
                     type="xsd:string" />
     </xsd:sequence>
     <xsd:attribute name="CustomerID" type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

To put the pieces together in VB .NET, I built a little test form. This first
test calls code to retrieve data from the local SQL Server, using the XSD file
as the specification for the data, and then drops the results into a DataSet.
From there, the DataSet gets displayed on a DataGrid control. To use any of the
SQLXML managed classes, you need to first set a reference to the
Microsoft.Data.SqlXml library, and then include an Imports
Microsoft.Data.SqlXml statement at the top of the module. With that done,
here’s the code from the button:

Private Sub btnLoadDataset_Click( _
 ByVal sender As System.Object, ByVal e As System.EventArgs) _
 Handles btnLoadDataset.Click
    ' Load XML data from SQL Server directly into a DataSet
    Dim strConn As String = "Provider=SQLOLEDB;" & _
     "Server=(local);database=Northwind;" & _
     "Integrated Security=SSPI"
    ' Define the XML command
    Dim cmd As SqlXmlCommand = New SqlXmlCommand(strConn)
    cmd.RootTag = "ROOT"
    cmd.CommandText = "Cust"
    cmd.CommandType = SqlXmlCommandType.XPath
    cmd.SchemaPath = "Customers.xsd"
    ' Load the DataSet
    Dim daSql As SqlXmlAdapter = New SqlXmlAdapter(cmd)
    Dim ds As DataSet = New DataSet
    daSql.Fill(ds)
    ' Display the results
    dgCustomers.DataSource = ds
    dgCustomers.DataMember = "Cust"
End Sub

Figure 1 shows the results. One of the nice things about this technique is
that it makes it easy to change, at runtime, which data will be retrieved. Just
edit the XSD file and the SqlXmlCommand object will pick up the new
data.

Retrieving Valid XML Data from SQL Server

My second example shows how to fix a problem with SQL Server’s native FOR XML
queries. FOR XML doesn’t actually return an XML document; instead, it returns an
XML fragmant, with no XML declaration or root element. This is a problem if you
want to feed this XML to classes that expect actual XML. Fortunately, you can
use the SqlXmlCommand object to execute a FOR XML query and wrap up
the results properly on their way in to your code.

For this example, I’ve constructed a FOR XML query that returns some customer
and order information. Using the SQLXML Managed Classes, I can retrieve the
results of this query straight to an XmlReader and display them on
the test form’s user interface. Here’s the code:

Private Sub btnLoadXML_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles btnLoadXML.Click
    ' Load proper XML from SQL Server
    Dim strConn As String = "Provider=SQLOLEDB;" & _
     "Server=(local);database=Northwind;" & _
     "Integrated Security=SSPI"
    ' Define the XML command
    Dim cmd As SqlXmlCommand = New SqlXmlCommand(strConn)
    ' Set up a SqlXmlCommand with a FOR XML query
    cmd.CommandType = SqlXmlCommandType.Sql
    cmd.CommandText = _
     "SELECT Customers.CompanyName, " & _
     "Customers.ContactName, " & _
     "Orders.OrderID, Orders.OrderDate " & _
     "FROM Customers INNER JOIN Orders " & _
     "ON Customers.CustomerID = " & _
     "Orders.CustomerID " & _
     "FOR XML AUTO, ELEMENTS"
    ' Specify a root element for the result
    cmd.RootTag = "dataroot"
    ' Read the XML into an XmlReader
    Dim xr As XmlReader = _
     cmd.ExecuteXmlReader()
    ' Transfer it to an XmlDOcument
    Dim xd As XmlDocument = New XmlDocument
    xd.Load(xr)
    xr.Close()
    ' Get a Transform ready
    Dim xslt As XslTransform = New XslTransform
    xslt.Load("identity.xslt")
    ' Use the transform to pretty print it 
    Dim ms As MemoryStream = New MemoryStream
    xslt.Transform(xd, Nothing, ms)
    ' And drop the results to a TextBox control
    ms.Position = 0
    Dim sr As StreamReader = New StreamReader(ms)
    txtXML.Text = sr.ReadToEnd()
End Sub

Most of this is just standard .NET XML code. What SQLXML brings to the table
is the ability to get a true XML stream from a SQL Server query. That’s what the
ExecuteXmlReader method accomplishes.

The identity.xslt file, by the way, is a simple XSL stylesheet
that adds indentation to the input file without making any other changes. It’s
useful whenever you want to display XML in a prettier fashion:

<?xml version = "1.0"?>
<xsl:stylesheet xmlns_xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="node()|@*">
    <xsl:copy>
      <xsl:apply-templates select="node()|@*"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

A Good Interim Solution

Some time next year, SQL Server 2005 is going to deliver tightly-integrated
XML functionality, as well as much closer .NET integration. But that’s next
year, and you probably have applications to write now. Until the new stuff is
available, the SQLXML library provides a well-tested set of ways to work with
SQL Server 2000 and XML that you can use today.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD
70-306, and MCAD 70-310 Training Guides from Que Publishing. When he’s not
writing code, Mike putters in the garden on his farm in eastern Washington
state.