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.
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
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
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
- 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
- The SQLXML Managed Classes to expose SQLXML functionality to .NET
- 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
SqlXmlCommand, which allows you to send various XML-flavored
commands to SQL Server.
SqlXmlParameter, which provides parameter support for the
SqlXmlAdapter, which lets you easily hook up SQL Server XML
data to the standard ADO.NET
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 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
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
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.
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