Extend the reach of SQL Server with SQLXML classes in .NET

SQL Server 2000 provides XML features called SQLXML classes that are readily available without drastic changes to database design or related stored procedures. Here are a couple of examples of how to use these powerful features.

The term SQLXML is used to denote the XML features supported in SQL Server 2000 in .NET. These new XML features extend the reach of SQL Server, and their effects on the database developer are minimal. The classes in the Microsoft.Data.SqlXml namespace are referred to as the SQLXML-managed classes, and they were introduced in order to utilize these new XML features. In this article, I’ll discuss these classes along with their associated sample code.

The SQLXML-managed classes include the SqlXmlCommand, SqlXmlParameter, and the SqlXmlAdapter classes (see Figure A).

Figure A
SQLXML classes

The SqlXmlCommand class exposes a number of methods. Table A below shows some of the important methods and provides a description for each.
Table A
Method Description
void ExecuteNonQuery()  This method is used in situations where the command does not return anything.
Stream ExecuteStream() Use this method to get the results of the query in a new Stream object.
void ExecuteToStream(Stream outputstream) Use this method to write the results returned by a query into an existing Stream object.
XmlReader ExecuteXmlReader() This returns the results of the query as a XmlReader object.

The SqlXmlParameter class is used to create parameters passed to an SqlXmlCommand object. The parameter is created using the CreateParameter method of the SqlXmlCommand class. The SqlXmlParameter class exposes two properties, Name and Value.

This SqlXmlAdapter class forms the link between the Dataset class of the .NET Framework and the SqlXml managed classes. It is used to populate a Dataset object with the results obtained from an SqlXmlCommand object. Table B outlines the available methods.
Table B
Method Description
void File(DataSet ds)
This method fills the data from the result obtained using the SqlXmlCommand Object.
void Update(DataSet ds)
This method is used for updating the SQL Server database with data in the Dataset object.

Utilizing the SQLXML classes in .NET
Now I’ll examine the integration of SQLXML-managed classes into .NET applications. The examples below take advantage of the ever-present Northwind database provided with every SQL Server installation.

Creating the sample application
To create a sample application, you need to open a new Web application. The first step to using the SQLXML-managed classes is to add a reference to the Microsoft.Data.SqlXml assembly to your project. Also, remember to add the Microsoft.Data.SqlXml directive to the class in which SQLXML-managed classes are utilized. (See Figure B.)

Figure B

The ClientSideXml feature
One of the most noteworthy features provided by the SQLXML-managed classes is the ability to process a returned result into XML on the client side. To demonstrate, I’ll convert the result from a stored procedure to XML. I am using the ”Ten Most Expensive Products” stored procedure, which lists the 10 most expensive products from the products table in the Northwind database.

The original output from this stored procedure is 10 rows containing the list of the 10 most expensive products. Since this is an existing stored procedure, no coding is involved until this step. The C# code fragment in Listing A shows how an SqlXmlCommand object is used to retrieve data from a stored procedure, and how it is converted to XML on the client side.

Next, the ClientSideXml property of the SqlXmlCommand object is set to true. The stored procedure name is passed to the EXEC command along with the FOR XML NESTED option. The FOR XML NESTED option is similar to the FOR XML AUTO option. The RootTag property is used to specify the root level tag.

The advantage of this functionality is that you get XML from existing stored procedures written before the advent of XML without modifying any of the stored procedure code.

Using XPath for querying
Another useful feature provided by the SQLXML-managed classes is the utilization of XPath. XPath is a language used to identify specific parts of an XML document. You can execute XPath queries against a schema mapped to the fields of a database.

To do so, you first need to define a schema for the database objects. The schema in Listing B represents three fields from the products table in the Northwind database. These include ProductName, UnitPrice, and ProductID.

It is also possible to make XPath queries against this mapping schema. The database results are based on the schema shown in Listing B. Listing C provides a brief look at the C# code.

In the code fragment, I created the SqlXmlCommand object and set the value of CommandText property to the XPath query. Here is the XPath query in code:
XPath Query: Products[@UnitPrice<=30.0]

This query retrieves all products from the Products Table for which the UnitPrice column is less than or equal to a value of 30.0. Next, the CommandType property is set to SqlXmlCommandType.XPath to indicate that an XPath query is being used. Also, you’ll need to provide the path for the schema against which you’ll make the query. This is accomplished by pointing the SchemaPath property to the Products.xsd file. Once these settings are in place, call the ExecuteStream method of the SqlXmlCommand object to retrieve the results from the table as a stream. The results are loaded into an XmlDocument object for further processing.

Editor's Picks