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:
- On December 19, 2000, SQL Server Magazine posted a new zip file that includes a corrected version of the usp_GetCatTotals stored procedure in Listing 1. The ORDER BY clause in this stored procedure should read: ORDER BY [Product!2!ProductName]. We apologize for any inconvenience this error may have caused.