Editor's Note: Send your XML questions to Rich Rollman at xmlquestions@sqlmag.com.
Let's look at several follow-up questions about some of the subjects I covered in previous columns. If you're new to Exploring XML, you can find these columns on the Web at http://www.sqlmag.com/articles/index.cfm?authorid=1059.
Transforming a SQL Server 7.0 Recordset into XML
What's the best way to transform a returned recordset into an XML document? I need to use SQL Server 7.0 to accomplish this transformation and have it work just as efficiently in SQL Server 2000.
In December 2000, I explored using Microsoft Access and SQL Server releases prior to SQL Server 2000 with XML. That column provides a detailed explanation and code samples for how to use the ADO Persistence Provider to generate XML code. Although the ADO Persistence Provider was the best solution at the time, a better solution should be available by the time you read this article. Sources at Microsoft confirm that the company is working to provide the FOR XML syntax for SQL Server 7.0 (and possibly even earlier SQL Server releases), with an anticipated availability date of late third quarter 2001. FOR XML support for other databases, including Access, depends on the final implementation details. (For more information about FOR XML support, see the Microsoft XML Developer Center at http://msdn.microsoft.com/xml.)
Querying a Column That Contains an XML Document
I've seen how you can use a FOR XML query to retrieve XML from the database. Can you also query a SQL Server column that contains an XML document and use an Extensible Style Language (XSL) stylesheet from an Active Server Pages (ASP) page to display the SQL Server column?
In February 2001, I showed how you can use OpenXML's @mp:xmltext directive to store XML fragments or documents in a database column. In ADO, you can use a SQL query without a FOR XML clause to query the column's value and retrieve the appropriate XML document from the database. The Microsoft XML Parser (MSXML) then parses the column's value and uses the stylesheet to transform that value, as the code in Listing 1 shows.
An alternative approach obtains the same result, avoids the parsing code, and invokes the Extensible Style Language Transformations (XSLT) transformation. This approach uses a technique similar to the one I described in July 2001. An ADO CommandStream interface can pass an XML document to a stored procedure or process an XML template. (For more information about XML templates, see SQL Server Books Online—BOL.) The template contains both the queries that retrieve XML from the database and a directive that specifies which XSL stylesheet to use after all embedded queries have run. Listing 2, page 62, shows the code to build a template that contains a query to return the value in the Composite table's xml column (i.e., an XML document).
Two items in the template are worthy of special note. First, when you use a query that doesn't contain a FOR XML clause in a template, the query's result must contain only one column—and that column must contain XML. Second, when you use the template with the SQL Server Internet Server API (ISAPI) DLL (i.e., the SQL Server XML Support for Microsoft IIS), the template produces the same result as the ADO CommandStream solution but requires no code beyond the template.