Nested FOR XML Queries

Article
12/03/2008

In SQL Server 2000, you can specify the FOR XML clause only at the top level of a SELECT query. The resulting XML is returned primarily to the client for additional processing. In SQL Server 2005, with the introduction of the xml Data Type and the TYPE Directive in FOR XML Queries, the XML returned by the FOR XML queries can be additionally processed on the server.

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.

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 (xml Data Type).

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 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 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" />

Also, you can write nested FOR XML queries where the result of the inner query is returned as 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.
The 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:
```
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" />
```
  By specifying the ELEMENTS directive, you can retrieve all the XML as element-centric. 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>
```
  For nested FOR XML queries in SQL Server 2005, you can construct XML that is partly attribute-centric and partly element-centric.
- In SQL Server 2000, you can construct siblings only by writing queries using EXPLICIT mode. However, this can be cumbersome. In SQL Server 2005, you can generate XML hierarchies that include siblings by specifying nested AUTO mode FOR XML queries.
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

A. Comparing a FOR XML query with a nested FOR XML query

The following SELECT query retrieves product category and subcategory information in the AdventureWorks database. There is no nested FOR XML in the query.

SELECT   ProductCategory.ProductCategoryID, 
         ProductCategory.Name as CategoryName,
         ProductSubCategory.ProductSubCategoryID, 
         ProductSubCategory.Name
FROM     Production.ProductCategory, Production.ProductSubCategory
WHERE    ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE
GO

This is the partial result:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory ProductSubCategoryID="1" Name="Mountain Bike"/>
  <ProductSubCategory ProductSubCategoryID="2" Name="Road Bike"/>
  <ProductSubCategory ProductSubCategoryID="3" Name="Touring Bike"/>
</ProductCategory>
...

If you specify the ELEMENTS directive in the query, you receive an element-centric result, as shown in the following result fragment:

<ProductCategory>
  <ProductCategoryID>1</ProductCategoryID>
  <CategoryName>Bike</CategoryName>
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <Name>Mountain Bike</Name>
  </ProductSubCategory>
  <ProductSubCategory>
     ...
  </ProductSubCategory>
</ProductCategory>

Next, assume that you want to generate an XML hierarchy that is a combination of attribute-centric and element-centric XML, as shown in the following fragment:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

In the previous fragment, product category information such as category ID and category name are attributes. However, the subcategory information is element-centric. To construct the <ProductCategory> element, you can write a FOR XML query as shown in the following:

SELECT ProductCategoryID, Name as CategoryName
FROM Production.ProductCategory ProdCat
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

This is the result:

< ProdCat ProductCategoryID="1" CategoryName="Bikes" />
< ProdCat ProductCategoryID="2" CategoryName="Components" />
< ProdCat ProductCategoryID="3" CategoryName="Clothing" />
< ProdCat ProductCategoryID="4" CategoryName="Accessories" />

To construct the nested <ProductSubCategory> elements in the XML you want, you then add a nested FOR XML query, as shown in the following:

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName
        FROM   Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

Note the following in the previous query:

The inner FOR XML query retrieves product subcategory information. The ELEMENTS directive is added in the inner FOR XML to generate element-centric XML that is added to the XML generated by the outer query. By default, the outer query generates attribute-centric XML.
In the inner query, the TYPE directive is specified so the result is of xml type. If TYPE is not specified, the result is returned as nvarchar(max) type and the XML data is returned as entities.
The outer query also specifies the TYPE directive. Therefore, the result of this query is returned to the client as xml type.

This is the partial result:

<ProductCategory ProductCategoryID="1" CategoryName="Bike">
  <ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bike</SubCategoryName></ProductSubCategory>
  <ProductSubCategory>
     ...
  <ProductSubCategory>
     ...
</ProductCategory>

The following query is just an extension of the previous query. It shows the full product hierarchy in the AdventureWorks database. This includes the following:

Product categories
Product subcategories in each category
Product models in each subcategory
Products in each model

You might find the following query useful in understanding the AdventureWorks database:

SELECT ProductCategoryID, Name as CategoryName,
       (SELECT ProductSubCategoryID, Name SubCategoryName,
               (SELECT ProductModel.ProductModelID, 
                       ProductModel.Name as ModelName,
                       (SELECT ProductID, Name as ProductName, Color
                        FROM   Production.Product
                        WHERE  Product.ProductModelID = 
                               ProductModel.ProductModelID
                        FOR XML AUTO, TYPE)
                FROM   (SELECT distinct ProductModel.ProductModelID, 
                               ProductModel.Name
                        FROM   Production.ProductModel, 
                               Production.Product
                        WHERE  ProductModel.ProductModelID = 
                               Product.ProductModelID
                        AND    Product.ProductSubCategoryID = 
                               ProductSubCategory.ProductSubCategoryID) 
                                  ProductModel
                FOR XML AUTO, type
               )
        FROM Production.ProductSubCategory
        WHERE ProductSubCategory.ProductCategoryID = 
              ProductCategory.ProductCategoryID
        FOR XML AUTO, TYPE, ELEMENTS
       )
FROM Production.ProductCategory
ORDER BY ProductCategoryID
FOR XML AUTO, TYPE

This is the partial result:

<Production.ProductCategory ProductCategoryID="1" CategoryName="Bikes">
  <Production.ProductSubCategory>
    <ProductSubCategoryID>1</ProductSubCategoryID>
    <SubCategoryName>Mountain Bikes</SubCategoryName>
    <ProductModel ProductModelID="19" ModelName="Mountain-100">
      <Production.Product ProductID="771" 
                ProductName="Mountain-100 Silver, 38" Color="Silver" />
      <Production.Product ProductID="772" 
                ProductName="Mountain-100 Silver, 42" Color="Silver" />
      <Production.Product ProductID="773" 
                ProductName="Mountain-100 Silver, 44" Color="Silver" />
        …
    </ProductModel>
     …

If you remove the ELEMENTS directive from the nested FOR XML query that generates product subcategories, the whole result is attribute-centric. You can then write this query without nesting. The addition of ELEMENTS results in an XML that is partly attribute-centric and partly element-centric. This result cannot be generated by a single-level, FOR XML query.

B. Generating siblings by using a nested AUTO mode query

The following example shows how to generate siblings by using a nested AUTO mode query. The only other way to generate such XML is to use the EXPLICIT mode. However, this can be cumbersome.

This query constructs XML that provides sales order information. This includes the following:

Sales order header information, SalesOrderID, SalesPersonID, and OrderDate. AdventureWorks stores this information in the SalesOrderHeader table.
Sales order detail information. This includes one or more products ordered, the unit price, and the quantity ordered. This information is stored in the SalesOrderDetail table.
Sales person information. This is the salesperson who took the order. The SalesPerson table provides the SalesPersonID. For this query, you have to join this table to the Employee table to find the name of the sales person.

The two distinct SELECT queries that follow generate XML with a small difference in shape.

The first query generates XML in which <SalesPerson> and <SalesOrderHeader> appear as sibling children of <SalesOrder>:

SELECT 
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID = 
                   SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
        FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
        for xml auto, type),
        (SELECT * 
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As 
                     SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE

In the previous query, the outermost SELECT statement does the following:

Queries the rowset, SalesOrder, specified in the FROM clause. The result is an XML with one or more <SalesOrder> elements.
Specifies AUTO mode and the TYPE directive. AUTO mode transforms the query result into XML, and the TYPE directive returns the result as xml type.
Includes two nested SELECT statements separated by a comma. The first nested SELECT retrieves sales order information, header and details, and the second nested SELECT statement retrieves salesperson information.
- The SELECT statement that retrieves SalesOrderID, SalesPersonID, and CustomerID itself includes another nested SELECT ... FOR XML statement (with AUTO mode and TYPE directive) that returns sales order detail information.

The SELECT statement that retrieves the sales person information queries a rowset, SalesPerson, created in the FROM clause. For FOR XML queries to work, you must provide a name for the anonymous rowset generated in the FROM clause. In this case, the name provided is SalesPerson.

This is the partial result:

<SalesOrder>
  <Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
    <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  </Sales.SalesOrderHeader>
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</SalesOrder>
...

The following query generates the same sales order information, except that in the resulting XML, the <SalesPerson> appears as a sibling of <SalesOrderDetail>:

<SalesOrder>
    <SalesOrderHeader ...>
          <SalesOrderDetail .../>
          <SalesOrderDetail .../>
          ...
          <SalesPerson .../>
    </SalesOrderHeader>
    
</SalesOrder>
<SalesOrder>
  ...
</SalesOrder>

This is the query:

SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT * 
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID=43659 or SalesOrderID=43660
FOR XML AUTO, TYPE

This is the result:

<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="676">
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="776" OrderQty="1" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="777" OrderQty="3" UnitPrice="2024.9940" />
  <Sales.SalesOrderDetail SalesOrderID="43659" ProductID="778" OrderQty="1" UnitPrice="2024.9940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="117">
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
  <Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
  <SalesPerson SalesPersonID="279" EmployeeID="279" />
</Sales.SalesOrderHeader>

Because the TYPE directive returns a query result as xml type, you can query the resulting XML by using various xml data type methods. For more information, see xml Data Type Methods. In the following query, note the following:

The previous query is added in the FROM clause. The query result is returned as a table. Note the XmlCol alias that is added.

The SELECT clause specifies an XQuery against the XmlCol returned in the FROM clause. The query() method of the xml data type is used in specifying the XQuery. For more information, see query() Method (xml Data Type).

SELECT XmlCol.query('<Root> { /* } </Root>')
FROM (
SELECT SalesOrderID, SalesPersonID, CustomerID,
             (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
              from Sales.SalesOrderDetail
              WHERE SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
              FOR XML AUTO, TYPE),
              (SELECT * 
               FROM  (SELECT SalesPersonID, EmployeeID
                    FROM Sales.SalesPerson, HumanResources.Employee
                    WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
               WHERE  SalesPerson.SalesPersonID = SalesOrderHeader.SalesPersonID
         FOR XML AUTO, TYPE)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID='43659' or SalesOrderID='43660'
FOR XML AUTO, TYPE ) as T(XmlCol)

C. Creating an ASPX application to retrieve sales order information in the browser

In the following example, an aspx application executes a stored procedure and returns sales order information as XML. The result is shown in the browser. The SELECT statement in the stored procedure is similar to that in example B, except that the resulting XML is element-centric.

CREATE PROC GetSalesOrderInfo AS
SELECT 
      (SELECT top 2 SalesOrderID, SalesPersonID, CustomerID,
         (select top 3 SalesOrderID, ProductID, OrderQty, UnitPrice
           from Sales.SalesOrderDetail
            WHERE  SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
            FOR XML AUTO, TYPE)
      FROM  Sales.SalesOrderHeader
        WHERE SalesOrderHeader.SalesOrderID = SalesOrder.SalesOrderID
      for xml auto, type),
        (SELECT * 
         FROM  (SELECT SalesPersonID, EmployeeID
              FROM Sales.SalesPerson, HumanResources.Employee
              WHERE SalesPerson.SalesPersonID = Employee.EmployeeID) As SalesPerson
         WHERE  SalesPerson.SalesPersonID = SalesOrder.SalesPersonID
       FOR XML AUTO, TYPE, ELEMENTS)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
      FROM Sales.SalesOrderHeader, Sales.SalesPerson
      WHERE SalesOrderHeader.SalesPersonID = SalesPerson.SalesPersonID
     ) as SalesOrder
ORDER BY SalesOrder.SalesOrderID
FOR XML AUTO, TYPE
GO

This is the .aspx application. It executes the stored procedure and returns XML in the browser:

<%@LANGUAGE=C# Debug=true %>
<%@import Namespace="System.Xml"%>
<%@import namespace="System.Data.SqlClient" %><%
Response.Expires = -1;
Response.ContentType = "text/xml";
%>

<%
using(System.Data.SqlClient.SqlConnection c = new System.Data.SqlClient.SqlConnection("Data Source=server;Database=AdventureWorks;Integrated Security=SSPI;"))
using(System.Data.SqlClient.SqlCommand cmd = c.CreateCommand())
{
   cmd.CommandText = "GetSalesOrderInfo";
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Connection.Open();
   System.Xml.XmlReader r = cmd.ExecuteXmlReader();
   System.Xml.XmlTextWriter w = new System.Xml.XmlTextWriter(Response.Output);
   w.WriteStartElement("Root");
   r.MoveToContent();
   while(! r.EOF)
   {
      w.WriteNode(r, true);
   }
   w.WriteEndElement();
   w.Flush();
}
%>

To test the application

Create the stored procedure in the AdventureWorks database.
Save the .aspx application in the c:\inetpub\wwwroot directory (GetSalesOrderInfo.aspx).
Execute the application (https://server/GetSalesOrderInfo.aspx).

D. Constructing XML that includes product prices

The following example queries the Production.Product table to retrieve the ListPrice and StandardCost values of a specific product. To make the query interesting, both prices are returned in a <Price> element, and each <Price> element has a PriceType attribute. This is the expected shape of the XML:

<xsd:schema xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" elementFormDefault="qualified">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="Production.Product" type="xsd:anyType" />
</xsd:schema>
<Production.Product xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" ProductID="520">
  <Price  PriceType="ListPrice">133.34</Price>
  <Price  PriceType="StandardCost">98.77</Price>
</Production.Product>

This is the nested FOR XML query:

SELECT Product.ProductID, 
          (SELECT 'ListPrice' as PriceType, 
                   CAST(CAST(ListPrice as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE),
          (SELECT  'StandardCost' as PriceType, 
                   CAST(CAST(StandardCost as NVARCHAR(40)) as XML) 
           FROM    Production.Product Price 
           WHERE   Price.ProductID=Product.ProductID 
           FOR XML AUTO, TYPE)
FROM Production.Product
WHERE ProductID=520
for XML AUTO, TYPE, XMLSCHEMA

Note the following from the previous query:

The outer SELECT statement constructs the <Product> element that has a ProductID attribute and two <Price> child elements.
The two inner SELECT statements construct two <Price> elements, each with a PriceType attribute and XML that returns the product price.
The XMLSCHEMA directive in the outer SELECT statement generates the inline XSD schema that describes the shape of the resulting XML.

To make the query interesting, you can write the FOR XML query and then write an XQuery against the result to reshape the XML, as shown in the following query:

SELECT ProductID, 
 ( SELECT p2.ListPrice, p2.StandardCost
   FROM Production.Product p2 
   WHERE Product.ProductID = p2.ProductID
   FOR XML AUTO, ELEMENTS XSINIL, type ).query('
                                   for $p in /p2/*
                                   return 
                                    <Price PriceType = "{local-name($p)}">
                                     { data($p) }
                                    </Price>
                                  ')
FROM Production.Product
WHERE ProductID = 520
FOR XML AUTO, TYPE

The previous example uses the query() method of the xml data type to query the XML returned by the inner FOR XML query and construct the expected result.

This is the result:

<Production.Product ProductID="520">
  <Price PriceType="ListPrice">133.3400</Price>
  <Price PriceType="StandardCost">98.7700</Price>
</Production.Product>

Nested FOR XML Queries

Examples

A. Comparing a FOR XML query with a nested FOR XML query

B. Generating siblings by using a nested AUTO mode query

C. Creating an ASPX application to retrieve sales order information in the browser

To test the application

D. Constructing XML that includes product prices

Additional resources