Use ADO 2.6 to exploit XML in SQL Server 2000
With the release of SQL Server 2000, Microsoft has started to make good on its new mission statement: to provide access to data and applications "anywhere, anytime, and on any device." At the center of this vision is support for XML, which Microsoft integrated into SQL Server 2000. XML's self-describing nature and cross-platform capabilities give SQL Server the ability to easily exchange data with Web applications and other systems. Paul Burke's "XML and SQL Server 2000," May 2000, and Bob Beauchemin's "The XML Files," September 2000, gave you overviews of SQL Server 2000's XML features. Now, let's dig into how OLE DB and ADO 2.6 support three key XML features: retrieving XML from stored procedures, executing templates, and executing XPath queries against XML views.
FOR XML
Before exploring the mechanics of using ADO to retrieve XML, let's quickly review how SQL Server 2000 produces XML. SQL Server 2000 introduces the FOR XML extension to the T-SQL SELECT statement. You simply append the FOR XML clause to a SELECT statement, and SQL Server returns the results formatted as XML, based on the parameters you pass to the statement. The most important argument in the FOR XML clause is XML mode, which can be RAW, AUTO, or EXPLICIT. The syntax for a SELECT statement with a FOR XML clause looks like this:
SELECT ...
FROM ...
[WHERE]
FOR XML RAW | AUTO | EXPLICIT [,XMLDATA] [,ELEMENTS] [,BINARY Base64]
To see what the three XML modes do and the kind of XML they produce, let's walk through each one.
XML RAW. XML RAW produces nonhierarchical, generic XML by generating one XML row element for each row that the query returns and mapping each returned column as an XML attribute. XML RAW is most useful when you have generic client code that expects flat XML documents and looks for row elements. Unlike XML AUTO, XML RAW supports the GROUP BY clause and aggregates. And all the XML modes let you return the XML Data Reduced (XDR) schema by using the XMLDATA argument after the FOR XML clause. The XDR schema is XML that describes the structure and contents of an XML document. The XDR schema lets your client code read data type information so that the code can properly format data. XDR schemas provide more information than Document Type Definition (DTD) files and are easier for parsers to work with. Note, however, that the XDR schema is Microsoft's proposal for an XML schema definition; the World Wide Web Consortium (W3C) hasn't approved XDR as a standard definition. Microsoft has said the company will support the W3C's XML schema definition when it becomes available. (For more information about XML schema definitions, see "XML Schema Part 2: Datatypes" at http://www.w3.org/tr/xmlschema-2.)
XML AUTO. XML AUTO produces a hierarchical document by transforming into an element each table that the SELECT clause references. By default, XML AUTO transforms each column into an attribute unless you use the ELEMENTS argument to create subelements. Keep in mind that XML AUTO doesn't support the GROUP BY clause; the column order in the SELECT statement determines the attributes' nesting order. XML AUTO lets you use table or column aliases as element or attribute names; however, this mode's default is to use the table or view name as the element or attribute name. You can use the BINARY Base64 argument to return image and binary data in binary base64-encoded format. If you don't use BINARY Base64, XML AUTO returns a URL that you can query to return binary data.
XML EXPLICIT. XML EXPLICIT is the most sophisticated and most powerful XML mode. As the name implies, with EXPLICIT mode, you explicitly define the schema for the returned XML by creating a virtual table that SQL Server translates into XML. Because this mode is so flexible, it's particularly good for creating hierarchical documents. EXPLICIT mode lets you define each column as an attribute or element and even create elements not represented in your database. When you use this mode, you must prefix the result set with two columns, Tag and Parent, which create the hierarchical structure of the resulting XML. As with the other modes, you also must specify the element and attribute names within the SELECT clause.
With the FOR XML clause, you can easily transform a result set into XML. However, you can use the clause only in statements that return data directly to a client. You can't use FOR XML in view or user-defined function (UDF) definitions, nested SELECT statements, stored procedures that manipulate the result set, INSERT statements, or statements that use a COMPUTE BY clause.
Returning XML with ADO
Now, let's examine a stored procedure that uses ADO to return XML to a client application. Listing 1 shows the usp_GetCatTotals stored procedure, which returns an XML document that aggregates order detail information from the Northwind database for a particular product category and time period. The procedure uses EXPLICIT mode to produce a customized schema with OrderTot as the top-level element. This element contains attributes that reflect the parameters categoryID, start date, and end date, which the client application passes into the stored procedure. This technique lets each client application that calls the procedure specify how SQL Server should create the result set. Figure 1 shows an XML document that usp_GetCatTotals produced.
Figure 2 shows an application that uses the three techniques that this article covers for using ADO to return XML from SQL Server 2000. The application, which I developed in Visual Basic (VB) 6.0, uses ADO 2.6 (the version that ships with SQL Server 2000) with the OLE DB provider for SQL Server, SQLOLEDB. You could easily adapt the application to Active Server Pages (ASP) without losing any functionality.
Listing 2 shows the first technique, which is to simply call the usp_GetCatTotals stored procedure to return XML for display in a Web-browser control. As with a call to any stored procedure, you must first create an ADO Connection object to establish a database connection, then create a Command object to construct the call to the stored procedure, as Callout A in Listing 2 shows. In this example, the calling VB procedure, OpenProc, accepts three parameters: pCatID, pStart, and pEnd. You pass these parameters to the procedure through ADO Parameter objects, which you create by using the Command object's CreateParameter method.
After you create the Command object, you need to create the object that will receive the resulting XML. Because the usp_GetCatTotals stored procedure's result set isn't an ADO Recordset object but rather an XML document, ADO returns the data in a Stream object. The Stream object, which ADO 2.5 introduced along with the Record object, lets ADO manipulate data through the IStream COM interface. This ability lets ADO interoperate with data sources that return semistructured data such as XML or binary data. To instruct ADO to return the results through a Stream object, you first instantiate and open a Stream object, then point the Command object at the Stream object by using the Command object's Output Stream dynamic property, as Callout B in Listing 2 shows. When you use an object that supports the IStream interface, you can also use any COM component that supports the IStream interface. This example uses the ASP Response object, which supports IStream, to send the results of the Command object directly to a browser application. The procedure then executes the Command object, passing it the constant adExecuteStream, which instructs ADO to send the results to the output stream.
To extract the XML document from the output stream to a string, you could call the Stream object's ReadText method. However, in this example, the VB procedure uses the Microsoft XML Parser (MSXML) 2.6 to load the XML document, then display it in the Web-browser control, as Callout C in Listing 2 shows. The VB procedure uses a form-level Document Object Model (DOM) DOMDocument26 object that it instantiates in the form's Load event. The procedure then uses its Load method to extract the XML from the Stream object and place the XML in the parser. This technique works well because you can pass a pointer that supports the IStream interface to the DOMDocument26 object's Load method. The procedure then uses the ShowXML procedure, which Web Listing 1 shows, to save the XML to a temporary file and load it in the Web browser OLE custom control (OCX) for display. (You can access Web Listing 1 by entering InstantDoc ID 15854 at http://www.sqlmag.com and clicking Download the code in the Article Information box.) Note that you could load the OCX directly from an object that supports IStream, but VB doesn't let you easily do this.
Although ADO 2.5 and later versions let you use the Recordset object's Save method to generate XML, letting the database handle this task simplifies your code and lets you avoid processing the data twice: first to return a standard ADO recordset, then to generate the XML on the middle tier.
Executing a Template
You can also generate XML from SQL Server 2000 by using ADO to execute a template. A template is simply an XML document that defines one or more SQL or XML Path Language (XPath) queries for SQL Server to process. Although you usually execute templates from a Web client (a technique called URL access), you can also execute templates from ADO on the client.