Editor's Note: Send your XML questions to Rich Rollman at [email protected]
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.
The code in Listing 3 takes advantage of ADO 2.6 and the SQL Server OLE DB provider's ability to accept an Output Stream parameter. This parameter's value can be any object that provides an IStream or ISequentialStream interface. Because the ASP Response object provides an IStream interface, you can pass the Response object to ADO, letting the transformed query result stream directly to the client. Listing 4 shows the XSL stylesheet that transforms the output into an HTML table.
Deciding When to Use XML
Can you give me some criteria for deciding when I should use SQL Server 2000's XML features?
Many readers have asked how to determine XML's relevance to their organizations and tasks. XML itself doesn't solve business problems; XML is an enabling technology that provides a ubiquitous, cross-platform way to exchange data.
Most applications exchange data. Viewing Web pages in a browser, transmitting purchase orders between companies, or getting a stock quote from your favorite Internet startup company all require two or more parties to exchange data. The parties must agree both on the protocol to use to exchange data and on that data's format. XML's inherent properties make it a natural format for the data. For example, you can use XML as a replacement for EDI in cross-enterprise data exchange. Writing programs that use the tag-based XML format is easier, more flexible, and more extensible than using a position-based format such as EDI. XML is also more cost-effective than a position-based format because you can transmit XML across the Internet instead of through expensive value-added networks (VANs), many of which charge per-character transmission costs. XML simplifies such tasks as producing a purchasing system that services a company and its vendors and creating a sales reporting functionality that aggregates data across multiple divisions. XML makes such systems ubiquitous across platforms and easier to build and maintain than they were without XML.
These tasks will become easier still as second-generation XML technologies—which have more integrated tools and operating environments—come to market. New technologies such as Universal Description, Discovery, and Integration (UDDI), Web Services Description Language (WSDL), Simple Object Access Protocol (SOAP), and Microsoft .NET provide higher-level abstractions on top of XML's enabling technology. These technologies, and others to come, provide common programming abstractions that use XML. A programming abstraction lets you use XML as the data-transmission format without having to write code to produce the appropriate format.
But you don't need to wait for next-generation technology. XML-based Web services exist today. AccuWeather, for example, provides local weather forecasts in XML format. Screaming Media generates news feeds in XML format. Your favorite Web sites might use XML and Extensible Style Language Transformations (XSLT) to generate their content dynamically. Your local bank or credit union might even provide your account statement in XML format. If your work involves the exchange of data, XML is an ideal choice for the data format.
Specifying a VB Data Type for Passing an XML Document as a Parameter
Which data type should I specify in Visual Basic (VB) when passing an XML document as a parameter to a SQL Server stored procedure?
In July 2001, I examined how you use an XML template with an ADO CommandStream when passing an XML document as a parameter to a SQL Server stored procedure. Although this technique works well for stored procedures that return a resultset, you need a different technique if the stored procedure returns just a return value (e.g., the number of records inserted into the database).
An alternative to using ADO CommandStream is to pass the XML document as a parameter. For this approach, load the XML document into a string, create an ADO parameter, and assign the string to the parameter. The type of ADO parameter you create depends on the data type that the stored procedure uses when it declares the parameter. Choose the wide-character (Unicode) variations of the parameter types—adWChar, adVarWChar, and adLongVarWChar—because VB and most scripting languages provide support for Unicode strings. Table 1 shows the mappings between the SQL Server, OLE DB, and ADO data types that you use to pass a parameter to a stored procedure. The most flexible choice is to use ntext as the parameter's data type in the stored procedure because ntext accepts Unicode text and strings (e.g., XML documents) of arbitrary size. Listing 5 shows the VB sample code to create an ADO parameter and pass it to a stored procedure. (The downloadable vesion of this listing, available at http://www.sqlmag.com, includes a VB project and code that lets you pass XML documents as parameters. For download information, see "More on the Web.") Listing 6 presents the sample runOpenXMLwithReturn stored procedure that the VB program calls.