ADO and XML

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.

Listing 3 shows a template that calls the usp_GetCatTotals stored procedure and passes it a set of parameters. The syntax for calling a stored procedure from a template is similar to that for calling a stored procedure from T-SQL: You first define the parameters and their values, then use the EXEC statement to pass the parameters to the stored procedure. Your template must include the ROOT element, as Callout A in Listing 3 shows, although you can name the element something other than ROOT. After using the Connection object to create the connection to SQL Server and setting up a Command object, you write the template's contents into an ADO Stream object that the Command object will later execute. To write the contents into the Stream object, you instantiate and open a Stream object, then use the Stream object's WriteText method to insert the template into the Stream object, as Callout B in Listing 3 shows. The last line of code in this callout uses the Position property to reset the Stream pointer to the beginning of the Stream object so that subsequent reads from the stream will return data.

Before executing the Command object, the template uses the CommandStream property to associate the Stream object that contains the template with the Command object, as Callout C in Listing 3 shows. The CommandStream property lets an ADO Command object process the Stream contents as the source of the command. The template also sets the Dialect property to instruct ADO to interpret the Stream object's contents as XML. Like the earlier OpenProc VB procedure, the template returns the results in an output stream.

Using XML Views and XPath Queries

A third technique for generating XML through ADO uses XML Views and XPath queries. An XML View is an XML schema with additional information that lets the SQL Server query processor determine which tables to use when processing a query. You specify the schema by using XDR with special annotations that denote which schema elements map to which tables. You can also use annotations to specify element relationships, such as foreign keys, which let the SQLOLEDB provider create JOIN clauses to correctly return the data. Listing 4 shows the XDR schema with annotations that creates an XML View in which the top-level Order element contains child elements for Order Details and Customer data. Web Figure 1, available online, shows an XML document that the SQLOLEBDB provider generates from the XDR schema.

A discussion of annotation syntax is beyond this article's scope, but combining XDR schemas and annotations lets you create sophisticated schemas without having to code the FOR XML EXPLICIT syntax. SQL Profiler shows that when you use XDR schemas with annotations, the SQLOLEDB provider creates the FOR XML EXPLICIT syntax for you. In addition, if a trading partner sends you its schema to use, you can create an annotated version of the schema that your applications can execute against SQL Server to produce the XML document your trading partner needs. This process is far simpler than trying to use the FOR XML EXPLICIT statement to generate correctly formatted XML.

After you define the schema, you can issue XPath queries against it. XPath is a language that you use to select nodes in an XML document. (For more information about XPath, see the specification "XML Path Language (XPath) Version 1.0: W3C Recommendation 16 November 1999" at http://www.w3.org/tr/xpath.) Web Listing 2, available online, shows the stored procedure for executing the following XPath query against the schema in Listing 4 for a particular OrderID:

Order[@OrderID=?]

The first part of the query, Order, specifies the element to query. The second part, @OrderID=?, specifies the predicate attribute to query. The question mark denotes that you're going to provide a parameter to satisfy the OrderID. When using ADO to send an XPath query to SQL Server, you simply use the Command object's CommandText property and set the Dialect property so that ADO knows you're using an XPath query:

cmCmd.CommandText = "Order[@OrderID=" & pOrderID & "]"cmCmd.Dialect = "{EC2A4293-E898-11D2-B1B7-00C04F680C56}"

These statements pass the required OrderID to the XPath query through the pOrderID parameter.

The stored procedure must also give ADO the location of the XDR schema, also called the mapping schema. You specify the schema name in the Command object's dynamic Mapping Schema property and specify the file path in the Base Path property:

cmCmd.Properties("Mapping Schema") = "CustOrders.xml"cmCmd.Properties("Base Path") = App.Path & ""

This procedure also uses the VB App object to identify the base path because the mapping schema is located in the same directory as the VB executable file. Like the earlier examples, the stored procedure uses a Stream object to receive the returned XML.

Using these three ADO 2.6 features to return XML from SQL Server 2000—as opposed to hard-coding XML generation in a stored procedure or ASP page or using the ADO Recordset object's less efficient Save method—can significantly increase your productivity and let your SQL Server enterprise communicate easily in the connected world. As XML becomes more a part of your infrastructure, look for places to use these SQL Server 2000 and ADO 2.6 features.

Corrections to this Article: