XML Q&A - 20 Dec 2000

Editor's Note: Send your XML questions to Rich Rollman at [email protected].

The general theme of how ADO and XML are related continues this month as I answer questions about how to use XML outside a browser-based application and about the performance and relevance of the XML support in SQL Server 2000. These interesting questions address using XML and data-access technologies to build real-world applications.

I want to design a program to print data in formats that aren't possible within a Web browser. How can I use XML to populate the data stream of an application that is outside the browser environment?

Most examples that demonstrate SQL Server 2000's XML support use Web browsers as a convenient mechanism to display the XML results that a query produces. In real-world scenarios, the user almost never sees the XML. A style sheet processes the XML, or an application uses XML internally. SQL Server 2000's XML functionality is fully accessible to Visual Basic (VB), C++, and other COM-enabled programming languages through either the OLE DB 2.6 or ADO 2.6 interface. Let's look at how to obtain the XML results from a query in ADO.

The first step is allocating Stream, Connection, and Recordset objects. The ADO Stream object uses an input stream to send an XML template query to SQL Server and uses an output stream to obtain the result. Callout A in Listing 1 shows how to declare an ADO Stream object. Next, you configure the Connection object to access the database. Callout B in Listing 1 shows how to specify the XML dialect for the query. Now, set up the query by populating the input stream with an XML template (for more information about XML templates, see SQL Server Books Online—BOL—or go to http://msdn.microsoft.com/xml/default.asp). The query in Callout C uses the FOR XML syntax to instruct SQL Server to generate an XML output stream. This query will generate XML output in the stream specified by the Output Stream property. Callout D shows the code to set the Output Stream property.

As an alternative, you could replace the ADO Stream object with any object that supports the IStream or ISequentialStream interface. For example, you could use the Response object in an Active Server Pages (ASP) script. Finally, execute the command to obtain the XML results.

Your applications can now read the ADO Stream object that contains the formatted XML output stream, as Callout E shows, or you can pass the object to programs that will format the data to your liking.

Most articles I've read about SQL Server's XML functionality have focused on accessing SQL Server's XML capabilities through the HTTP protocol. But I think that most XML projects will use ADO to bring SQL Server data through an XML stream. After all, I don't intend to eliminate Microsoft IIS, and I don't plan to have my ADO components access SQL Server through HTTP. Do you expect that most of the real work with SQL Server and XML will be done through the ADO layer? Also, what is the performance difference between using an ADO Recordset and using an XML stream?

I don't share your viewpoint that most of the real work done with SQL Server and XML will use the ADO layer. Developers are building Web applications to use technologies such as HTML, Dynamic HTML (DHTML), JavaScript, Java, and COM. Web applications typically use HTTP because every Web server in existence supports it; plus, HTTP lets you transfer data through firewalls. Using a URL to retrieve data is simple and efficient for applications that query data. And retrieving data directly from an Internet Server API (ISAPI) extension supplied with SQL Server 2000 is more efficient for applications than invoking a middle-tier method call. This statement presupposes that no middle processing is required on the data, which is a safe assumption in many cases. But there's no religion here. Choose the method that works for you.

Regarding XML stream performance: Because of the overhead that character conversion incurs, generating an XML stream is necessarily slower than generating an ADO Recordset. But this is an apples-to-oranges comparison because you can't use ADO Recordsets across the Internet. To move the Recordset to a Web client, you first need to persist and serialize the Recordset, a process that incurs overhead similar to that incurred by generating an XML stream. Microsoft has optimized the XML support in SQL Server 2000 to generate XML more efficiently than you can by persisting ADO Recordsets or writing custom code to generate XML using Active Server Pages (ASP) or similar technologies.

So, let's compare the relative performance of SQL Server 2000 to generating XML by using ASP and JavaScript. In general, SQL Server 2000 will perform roughly 70 percent better when generating nonhierarchical data sets represented in XML. Performance increases significantly—as much as 300 percent to 400 percent—when SQL Server generates hierarchical results. No significant performance difference exists between using SQL Server 2000 and using the ADO persistence provider. But the ADO persistence provider can generate only a fixed schema, whereas SQL Server can also generate arbitrary XML grammars.

Can you recommend a basic XML book for beginners?

I can recommend two very good beginner XML books. The most generally applicable book for beginners is Neil Bradley's The XML Companion (Addison-Wesley, 2000). This book provides a good overview of XML, starting at its roots in Standard Generalized Markup Language (SGML) and moving forward. An equally good book is Professional XML (Wrox, 2000) by Mark Birbeck, et al. This is a weighty tome that goes beyond Bradley's book into subjects such as Simple API for XML (SAX), XML schemas, and Wireless Access Protocol (WAP). Beyond primers, Michael Kay's XSLT Programmer's Reference (Wrox, 2000) is a must-have for anyone who writes XSLT transformations or XPath expressions.

Unfortunately, I can't recommend any references for the XML features of SQL Server 2000 other than Books Online (BOL). Many books that cover SQL Server 2000's XML features are in progress, but at press time they weren't available for review.

Corrections to this Article: