Section 7.9. XML Results Using the FOR XML Clause

7.9. XML Results Using the FOR XML Clause

You can return the result set of a SELECT statement as XML by specifying the FOR XML clause in the query. The FOR XML clause was introduced in SQL Server 2000. SQL Server 2005 enhances the functionality, as discussed in the "FOR XML Updates and Enhancements" section later in this chapter. The FOR XML clause syntax is:

     [ FOR { BROWSE | <XML> } ]     <XML> ::=       XML       {         { RAW [ ('ElementName') ] | AUTO }         [           <CommonDirectives>           [ , { XMLDATA | XMLSCHEMA [ ( TargetNameSpaceURI ) ]} ]           [ , ELEMENTS [ XSINIL | ABSENT ]         ]         | EXPLICIT           [             <CommonDirectives>             [ , XMLDATA ]           ]         | PATH [ ('ElementName') ]           [             <CommonDirectives>             [ , ELEMENTS [ XSINIL | ABSENT ] ]           ]       }      <CommonDirectives> ::=        [ , BINARY BASE64 ]        [ , TYPE ]        [ , ROOT [ ('RootName') ] ] 

where:

RAW[(' ElementName')]

Transforms each row in the result set into an XML element with the name specified in the ElementName parameter. The identifier defaults to <row> if the ElementName parameter is not specified.

AUTO

Returns the query results as a simple, nested XML hierarchy. Each table in the FROM clause with at least one column selected is returned as an XML element.

XMLDATA

Returns an inline XML-Data Reduced (XDR) schema in the returned XML.

XMLSCHEMA [ ( TargetNameSpaceURI ) ]

Returns an inline XSD Schema prepended to the resulting XML document. If the TargetNameSpaceURI argument is specified, the specified namespace is returned in the schema. You cannot use the XMLSCHEMA directive with the ROOT directive or when a row tag name is specified.

ELEMENTS [ XSINIL | ABSENT ]

Specifies that columns are returned as subelements rather than mapped to attributes. The ELEMENTS option is supported only for RAW, AUTO, and PATH modes.

The XSINIL option specifies that an element with the xsi:nil attribute set to true is created for a column with a NULL value. If not specified, or the ABSENT option is specified, no element is created for a column with a NULL value.

EXPLICIT

Explicitly specifies the XML hierarchy for the query result.

PATH

A simpler way than EXPLICIT mode to specify the XML hierarchy for the result set. PATH uses nested FOR XML queries to mix elements and attributes and to specify the nesting used to represent complex properties. Attributes must appear before other node types in the same level.

BINARY BASE64

Specifies that any binary data returned by the query is represented in Base64-encoded format. This option must be specified when returning binary data using RAW or EXPLICIT mode. By default, binary data is returned as a reference in AUTO mode.

TYPE

Specifies that the results of the query are returned as an xml data type instance.

ROOT[(' RootName')]

Adds a single top-level (root) element to the returned XML result with the name specified by the RootName argument. If the RootName argument is not specified, the name of the top-level element defaults to root.

7.9.1. Some FOR XML Examples

The examples in this subsection show the effect of the FOR XML clause on the result set returned by the following SELECT statement:

     USE AdventureWorks     SELECT TOP 2 DepartmentID, Name     FROM HumanResources.Department 

The SELECT statement without the FOR XML clause returns the ID and name of the top two departments, as shown in Figure 7-20.

Figure 7-20. Results for SELECT example

Now add the FOR XML RAW clause to the statement:

     SELECT TOP 2 DepartmentID, Name     FROM HumanResources.Department     FOR XML RAW 

The result set is a single row with one xml data type column containing the XML fragment shown in Figure 7-21.

Figure 7-21. Results for FOR XML example

Add the ROOT directive to the FOR XML clause to add a root node Departments and turn the XML fragment into an XML document:

     SELECT TOP 2 DepartmentID, Name     FROM HumanResources.Department     FOR XML RAW, ROOT ('Departments') 

The results are shown in Figure 7-22.

Specifying the XMLSCHEMA directive returns an inline XSD schema in the result set:

     SELECT TOP 2 DepartmentID, Name     FROM HumanResources.Department     FOR XML RAW, XMLSCHEMA 

Figure 7-22. Results for FOR XML with ROOT directive example

The results are shown in Figure 7-23.

Figure 7-23. Results for FOR XML with XMLSCHEMA directive

The following example uses AUTO mode to return sales order information:

     SELECT soh.SalesOrderID, soh.OrderDate, soh.CustomerID,       sod.ProductID, sod.OrderQty     FROM Sales.SalesOrderHeader soh, Sales.SalesOrderDetail sod     WHERE soh.SalesOrderID = sod.SalesOrderID     FOR XML AUTO 

Partial results are shown in Figure 7-24.

One element is created for each table specified in the FROM clause, with the table aliases specified in the FROM clause used as element names. AUTO mode uses the column order in the SELECT statement to nest elements in the XML document hierarchy. Values of selected columns are added to the elements as attributes. The ORDER BY clause is needed to ensure that all child elements are nested under a single parent element.

The following example uses PATH mode to return contact information for vendors:

     SELECT         v.VendorID "@ID",         v.Name "@Name",         c.FirstName "Contact/First",         c.LastName "Contact/Last"     FROM Purchasing.Vendor v, Purchasing.VendorContact vc, Person.Contact c     WHERE v.VendorID = vc.VendorID AND vc.ContactID = c.ContactID     ORDER BY v.Name     FOR XML PATH ('Vendor') 

Figure 7-24. Results for FOR XML with AUTO mode example

Partial results are shown in Figure 7-25.

Figure 7-25. Results for FOR XML with PATH mode example

The ampersand (@) preceding the VendorID and Name column names results in the output of attributes in the XML document. The slash (/) in the FirstName and LastName column names results in the output of XML subelements in the XML document.

The following example uses EXPLICIT mode to return contact information for vendors:

     SELECT DISTINCT         1           AS Tag,         NULL        AS Parent,         v.VendorID  AS [Vendor!1!ID],         v.Name      AS [Vendor!1!Name],         NULL        AS [Contact!2!FirstName],         NULL        AS [Contact!2!LastName]     FROM Purchasing.Vendor v, Purchasing.VendorContact vc, Person.Contact c     WHERE v.VendorID = vc.VendorID AND vc.ContactID = c.ContactID     UNION ALL     SELECT         2            AS Tag,         1            AS Parent,         v.VendorID,         v.Name,         c.FirstName,         c.LastName     FROM Purchasing.Vendor v, Purchasing.VendorContact vc, Person.Contact c     WHERE v.VendorID = vc.VendorID AND vc.ContactID = c.ContactID     ORDER BY [Vendor!1!ID], [Contact!2!LastName]     FOR XML EXPLICIT 

Partial results are shown in Figure 7-26.

Figure 7-26. Results for FOR XML with EXPLICIT mode example

The Tag and Parent metacolumns determine the XML document hierarchy. Columns are selected at each level of the hierarchy and combined into a nested XML document using the UNION ALL operator. The column name syntax is elementName! elementLevel!attributeName.

7.9.2. FOR XML Support for SQL Server Data Types

The following SQL Server data types have limitations or special handling as described when used with the FOR XML clause:

xml

If an xml data type is specified in the SELECT clause, column values are mapped to and serialized as elements in the returned XML regardless of whether the ELEMENTS directive is specified. XML declarations in the xml data type column are not serialized.

User-Defined Types (UDT)

CLR UDTs are not supported.

String

Whitespace characters in the data are entitized. For example, carriage returns, tabs, and line feeds are converted to &#xd;, &#x09;, and &#xA;, respectively.

timestamp

timestamp data type instances are treated as varbinary(8) data and are always Base 64 encoded. If an XSD or XSR schema is requested, it reflects this.

7.9.3. FOR XML Updates and Enhancements

SQL Server 2005 updates and enhances FOR XML functionality in SQL Server 2000 as described in the following list:

TYPE directive

In SQL Server 2000, a FOR XML query returns results either as a text or image type. In SQL Server 2005, the TYPE directive lets you return a result set from a FOR XML query as an xml data type.

RAW mode enhancements

RAW mode now lets you specify the row element name, retrieve element-centric XML, and specify the root element for the XML result.

AUTO mode enhancements

AUTO mode shapes the returned XML hierarchy by comparing columns in adjacent rows in the query. In SQL Server 2000, ntext, text, and image data types are not compared. In SQL Server 2005, xml data type columns are also not compared. The new varchar(max), nvarchar(max), and varbinary(max) data types are compared.

Derived table support has been improved, allowing it to be used to group columns from different tables under the same element while hiding the join from the AUTO mode shaping mechanism.

EXPLICIT mode enhancements

EXPLICIT mode now supports the CDATA directive with an element name and the xsi:nil column mode.

Nested queries

Using the xml data type and the TYPE directive in FOR XML queries lets you further process the XML result set on the server. This lets you build nested FOR XML queries.

Generating elements for NULL values

The XSINIL parameter of the ELEMENTS directive lets you create elements for NULL column values. These elements have an xsi:nil attribute set to TRue.

Inline XSD and XDR schema generation

A query with a FOR XML clause can return an inline schema in the XML returned.

Specify the XMLSCHEMA keyword to return an XSD schema. XMLSCHEMA can be specified only in RAW or AUTO mode, not in EXPLICIT mode. A nested FOR XML query that specifies the TYPE directive returns an untyped xml data type instance.

Specify the XMLDATA keyword in the FOR XML clause to return an XDR schema. The XDR schema does not support all new data types and enhancements in SQL Server 2005. Specifically:

• XDR does not support FOR XML query result sets that include xml data type columns.

• The varchar(max), nvarchar(max), and varbinary(max) data types are mapped to varchar(n), nvarchar(n), and varbinary(n).

• When compatibility mode is set to 90, timestamp values are treated as varbinary(8) data and Base64-encoded when binary base64 is specified, and URL-encoded in AUTO mode when binary base64 is not specified.