The SELECT...FOR XML Statement

To help you retrieve XML data from the database, SQL Server 2000 provides an extension to the Transact-SQL SELECT statement in the form of the FOR XML keywords. By appending FOR XML to a SELECT statement, you can indicate to the SQL Server query processor that you want the results to be returned as an XML stream instead of a rowset. In addition to including the FOR XML keywords, you must also specify a mode to indicate the format of the XML that should be returned. This mode can be specified as RAW, AUTO, or EXPLICIT. Here’s the basic syntax for the SELECT…FOR XML statement:

 SELECT select_list FROM table_source WHERE search_condition FOR XML AUTO | RAW | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] 

You use the XMLDATA option to return an XML-Data Reduced (XDR) schema defining the document being retrieved. You use the ELEMENTS option with AUTO mode to return columns as subelements rather than as the default attributes, and you use BINARY BASE64 to specify that binary data should be returned in BASE64 encoding. We’ll look at each of these options at greater length later in this chapter.

Before we examine the SELECT…FOR XML statement in detail, you need to understand one important issue. The stream that’s returned by a SELECT…FOR XML query isn’t a complete XML document but an XML fragment containing an element for each row returned by the query. You must include code in the client application to add a root element to the stream to create a full, well-formed XML document. For example, the following XML fragment could be returned by a SELECT…FOR XML query:

 <OrderItem Order Product Quantity="12"/> <OrderItem Order Product Quantity="10"/> 

Well-Formed XML

The rules for describing data using XML are fairly strict. Although the rules can cause headaches when you re trying to figure out what s wrong with the document you ve created, they re necessary so that XML parsers can easily read and expose XML documents.

First, XML elements must be strictly nested: each opening tag must have a closing tag. Second, XML tags are case sensitive. When you re creating an element using an opening and a closing tag, the case used in the opening tag must match that of the closing tag. Third, all elements in the document must be contained within a single root element. You can have only one top-level element per document. Fourth, all subelements must be wholly contained within their parent element.

An XML document that obeys all these rules is described as being well formed.

This sample would be considered a valid XML document only if a root element was added to the fragment, as shown in the following example:

 <Invoice>     <OrderItem Order Product Quantity="12"/>     <OrderItem Order Product Quantity="10"/> </Invoice>