Editor's Note: Send your XML questions to Rich Rollman at xmlquestions@sqlmag.com.
I understand that you can use XML technologies in several ways to query and update SQL Server 2000 databases. Can you give me some guidelines about using one technology versus another?
You can obtain XML query results from SQL Server in five ways and update SQL Server in four ways. Let's look at each method in turn.
Query Technologies
The five ways of returning XML query results fall into two broad categories of query technology: the FOR XML extension to the T-SQL syntax and XPath queries using annotated schemas. Microsoft added the FOR XML clause to T-SQL so that you could generate XML output rather than a standard recordset from SQL Server. Until October 2001, three FOR XML modes existed: RAW, AUTO, and EXPLICIT. But with XML for SQL Server 2000 Web Release 2 (SQLXML 2.0), Microsoft introduced a fourth mode called NESTED. Each mode generates a different XML result. Let's look at each FOR XML mode and guidelines for its use followed by guidelines for using XPath and annotated schemas.
RAW mode. RAW mode generates one row element for each row of the result and includes the column data as the row element's attributes. RAW mode is appropriate for the simplest queries, which carry one requirement: The data has to be represented in string format within an XML document. And although RAW mode looks deceptively like the ADO persistence format, don't be fooled. Query results that you format by using RAW mode won't load into ADO. For information about the ADO persistence format, see XML Q&A, December 2000, InstantDoc 15867, and XML Q&A, January 2001, InstantDoc 16028.
AUTO mode. AUTO mode bases the XML it generates on the names of the tables, views, and columns and on the joins that the query uses. This mode is most appropriate when you can control the result's XML schema. The XML schema is the way you've structured the XML, including the hierarchical structure of the XML and the elements' and attributes' names. You can also use AUTO mode when you're trying to generate a particular XML schema from a query. By using table and column aliases, you can leverage AUTO mode's ability to rename elements and attributes, and you can control the hierarchy by carefully specifying the column order in your query. (For more information about using AUTO mode, see "Retrieving and Writing XML Data" in SQL Server 2000 Books OnlineBOL.) But if the XML schema requires sophisticated nesting or represents column data as a mix of elements and attributes, an EXPLICIT query or an XPath query is more appropriate. Finally, the result you obtain from AUTO mode queries depends on the database structure. If you change the structure later, you'll need to either modify the query in your application to account for the new table structure or add views to your database to mimic the previous table structure.
EXPLICIT mode. EXPLICIT mode is the most flexible method for generating XML query results, but it's also the most complicated to use. Consider using EXPLICIT mode only when you have an existing database, when the XML schema has been specified, and when AUTO mode won't produce the desired result. (For example, when you need to create XML that represents columns from two different tables in your database as attributes on the same element, AUTO mode might not return the result you expect.) However, if the XML result you're trying to construct is complex, building an EXPLICIT-mode query can be a daunting task, even for T-SQL gurus. Alternatively, you might consider using the XPath query and annotated schema support. But if you enjoy a challenge, you can use EXPLICIT mode to produce almost any XML document.
NESTED mode. You can use NESTED mode any time you can use AUTO mode. NESTED mode moves the process of expanding a query result into XML format from SQL Server to the middle tier. When you use NESTED mode, you can offload processing cycles from the machine running SQL Server, thereby increasing the scalability of your platform. However, NESTED mode has a few minor limitations over AUTO mode because not all metadata in the database is available on the middle tier.
XPath and annotated schemas. Annotated schemas let you define an XML view on your database, which you query by using XPath. When a schema that describes the desired XML result is available, XPath query with annotated schemas is a good choice for generating XML results from SQL Server. The advantage to using XPath and annotated schemas is that this option hides the complexity of building EXPLICIT queries. The disadvantage is that XPath isn't nearly as expressive a query language as T-SQL, and XPath—as well as the SQL Server implementation—contains many limitations and peculiarities. These differences include lack of support for the wildcard character (*), disallowing position-oriented XPath node references, limited support for data types, and the absence of the UNION operator. If your scenario requires a query that you can't express in the subset of XPath implemented in SQLXML, you'll need to write EXPLICIT mode queries to obtain the desired output. If you're a T-SQL programmer and you want to use this technology in your applications, you'll also need to learn XPath and understand XML schemas. Generally, learning XPath is a welcome challenge for us geeks, but you need to factor in time to climb the learning curve if you're new to XML technologies.