http://www.developer.com/

Back to article

Using the SQLXML Managed Classes


April 19, 2004

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.

DataSet filled by SQLXML

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.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date