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.
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
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
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