XML Query Templates

As I just mentioned, a Command object is always used (explicitly or implicitly) when you use ADO to access data. The data returned is specified in some sort of query or command executed at the data source through the Command object. For example, you could use a Transact-SQL statement or a stored procedure to access rowset data in a SQL Server database. The underlying OLE-DB provider translates this statement if necessary before sending it to the data source.

To execute a FOR XML query by means of ADO, you must use a specific query syntax to instruct the SQL Server 2000 OLE-DB provider (SQLOLEDB) to execute a FOR XML query, and you must specify how the resulting XML fragment should be rendered as a well-formed XML document. The SQLOLEDB provider used to access data in SQL Server requires that FOR XML queries be submitted as XML documents known as templates, each containing a reference to the Microsoft XML-SQL namespace. The root element of the document used to submit a query serves as the root element in the resulting well-formed XML document. For example, the following XML root tags could enclose a query that would return an XML Invoice document:

 <Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>  </Invoice> 

Submitting a FOR XML Query

The XML-SQL namespace defines the sql:query tag, which is used to enclose one or more FOR XML queries in the query template. For example, the following query template could be used to generate an invoice from order data in the Northwind database:

 <Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>     <sql:query>         SELECT SalesRecord.OrderID InvoiceNo,                SalesRecord.OrderDate,                 LineItem.ProductID,                 LineItem.UnitPrice,                 LineItem.Quantity          FROM Orders SalesRecord          JOIN [Order Details] LineItem          ON SalesRecord.OrderID = LineItem.OrderID          WHERE SalesRecord.OrderID = 10248          FOR XML AUTO     </sql:query> </Invoice> 

If you use an ADO Command object to execute this query, you would get the following well-formed XML document. Later in this chapter, we'll discuss how to use a Command object to execute XML queries.

 <?xml version='1.0'?> <Invoice xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <SalesRecord InvoiceNo="10248" OrderDate="1996-07-04T00:00:00">         <LineItem Product UnitPrice="14" Quantity="12"/>         <LineItem Product UnitPrice="9.8" Quantity="10"/>         <LineItem Product UnitPrice="34.8" Quantity="5"/>     </SalesRecord> </Invoice> 

A query template can contain multiple queries, the results of which are all enclosed in the root element. For example, the template could be used to return a list of items in a particular order and a total price.

 <Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>     <sql:query>         SELECT SalesRecord.OrderID InvoiceNo,                SalesRecord.OrderDate,                 LineItem.ProductID,                 LineItem.UnitPrice,                 LineItem.Quantity          FROM Orders SalesRecord          JOIN [Order Details] LineItem          ON SalesRecord.OrderID = LineItem.OrderID          WHERE SalesRecord.OrderID = 10248          FOR XML AUTO     </sql:query>     <sql:query>         SELECT Sum(UnitPrice) TotalPrice          FROM [Order Details]          WHERE OrderID = 10248          FOR XML RAW     </sql:query> </Invoice> 

The results from the two queries are combined in the following XML document:

 <?xml version='1.0'?> <Invoice xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <SalesRecord InvoiceNo="10248" OrderDate="1996-07-04T00:00:00">         <LineItem Product UnitPrice="14" Quantity="12"/>         <LineItem Product UnitPrice="9.8" Quantity="10"/>         <LineItem Product UnitPrice="34.8" Quantity="5"/>     </SalesRecord>     <row TotalPrice="58.6"/> </Invoice> 

The second query calculates an aggregate value, and so AUTO mode can't be used.

Calling a Stored Procedure

Rather than include the FOR XML query in the query template itself, you could define a stored procedure in the database that contains the necessary FOR XML query. For example, the following Transact-SQL statements could be used to create a stored procedure that returns invoice data:

 CREATE PROC GetInvoice @orderno int AS SELECT SalesRecord.OrderID InvoiceNo,        SalesRecord.OrderDate,         LineItem.ProductID,         LineItem.UnitPrice,         LineItem.Quantity  FROM Orders SalesRecord  JOIN [Order Details] LineItem  ON SalesRecord.OrderID = LineItem.OrderID  WHERE SalesRecord.OrderID = @orderno FOR XML AUTO 

The stored procedure could then be called in the sql:query element of a query template, as shown here:

 <Invoice xmlns:sql='urn:schemas-microsoft-com:xml-sql'>     <sql:query>EXEC GetInvoice 10248</sql:query> </Invoice>