Nested FOR XML Queries

Processing with xml Type Variables

You can assign the FOR XML query result to an xml type variable, or use XQuery to query the result, and assign that result to an xml type variable for more processing.

USE AdventureWorks2008R2;
GO
DECLARE @x xml;
SET @x=(SELECT ProductModelID, Name
        FROM Production.ProductModel
        WHERE ProductModelID=122 OR ProductModelID=119
        FOR XML RAW, TYPE);
SELECT @x;

-- Result

--<row ProductModelID="122" Name="All-Purpose Bike Stand" />

--<row ProductModelID="119" Name="Bike Wash" />

You can additionally process the XML returned in the variable, @x, by using one of the xml data type methods. For example, you can retrieve the ProductModelID attribute value by using the value() method.

DECLARE @i int;
SET @i = (SELECT @x.value('/row[1]/@ProductModelID[1]', 'int'));
SELECT @i;

In the following example, the FOR XML query result is returned as an xml type, because the TYPE directive is specified in the FOR XML clause.

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=119 OR ProductModelID=122
FOR XML RAW, TYPE,ROOT('myRoot');

This is the result:

<myRoot>

<row ProductModelID="122" Name="All-Purpose Bike Stand" />

<row ProductModelID="119" Name="Bike Wash" />

</myRoot>

Because the result is of xml type, you can specify one of the xml data type methods directly against this XML, as shown in the following query. In the query, the query() method (xml Data Type) is used to retrieve the first <row> element child of the <myRoot> element.

SELECT  (SELECT ProductModelID, Name
         FROM Production.ProductModel
         WHERE ProductModelID=119 OR ProductModelID=122
         FOR XML RAW, TYPE,ROOT('myRoot')).query('/myRoot[1]/row[1]');

This is the result:

<row ProductModelID="122" Name="All-Purpose Bike Stand" />

Returning Inner FOR XML Query Results to Outer Queries as xml Type Instances

You can write nested FOR XML queries where the result of the inner query is returned as an xml type to the outer query. For example:

SELECT Col1, 
       Col2, 
       ( SELECT Col3, Col4 
        FROM  T2
        WHERE T2.Col = T1.Col
        ...
        FOR XML AUTO, TYPE )
FROM T1
WHERE ...
FOR XML AUTO, TYPE;

Note the following from the previous query:

• The XML generated by the inner FOR XML query is added to the XML generated by the outer FOR XML.

• The inner query specifies the TYPE directive. Therefore, the XML data returned by the inner query is of xml type. If the TYPE directive is not specified, the result of the inner FOR XML query is returned as nvarchar(max) and the XML data is entitized.

Controlling the Shape of Resulting XML Data

Nested FOR XML queries give you more control in defining the shape of the resulting XML data. In SQL Server 2000, the RAW and AUTO mode queries generate attribute-centric XML by default. For example:

USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW;

This is the attribute-centric result:

<row ProductModelID="122" Name="All-Purpose Bike Stand" />

<row ProductModelID="119" Name="Bike Wash" />

Alternatively, you can retrieve all the XML as element-centric by specifying the ELEMENTS directive. For example:

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 OR ProductModelID=119
FOR XML RAW, ELEMENTS ;

This is the element-centric result:

<row>

<ProductModelID>122</ProductModelID>

<Name>All-Purpose Bike Stand</Name>

</row>

<row>

<ProductModelID>119</ProductModelID>

<Name>Bike Wash</Name>

</row>

In SQL Server 2000, therefore, you must choose either attribute-centric or element-centric XML as the result of a query. Beginning with SQL Server 2005, however, you can use nested FOR XML queries to construct XML that is partly attribute-centric and partly element-centric.

For more information about specifying both attribute-centric and element-centric XML with nested FOR XML queries, see FOR XML Query Compared to Nested FOR XML Query and Shaping XML with Nested FOR XML Queries.

In SQL Server 2000, you can construct siblings only by writing queries using EXPLICIT mode. However, this can be cumbersome. Beginning with SQL Server 2005, you can generate XML hierarchies that include siblings by specifying nested AUTO mode FOR XML queries. For more information, see Generating Siblings by Using a Nested AUTO Mode Query.

Regardless of which mode you use, nested FOR XML queries provide more control in describing the shape of the resulting XML. They can be used in the place of EXPLICIT mode queries.

Examples

The following topics provide examples of nested FOR XML queries.

• FOR XML Query Compared to Nested FOR XML Query
  Compares a single-level FOR XML query to a nested FOR XML query. This example includes a demonstration of how to specify both attribute-centric and element-centric XML as the result of the query.

• Generating Siblings by Using a Nested AUTO Mode Query
  Shows how to generate siblings by using a nested AUTO mode query

• Using Nested FOR XML Queries in ASP.NET
  Demonstrates how an ASPX application can use FOR XML to return XML from SQL Server.

• Shaping XML with Nested FOR XML Queries
  Shows how to use nested FOR XML queries to control the structure of an XML document created by SQL Server.