---
title: "Use Nested FOR XML Queries | Microsoft Docs"
ms.custom: ""
ms.date: "03/14/2017"
ms.prod: sql
ms.prod_service: "database-engine"
ms.reviewer: ""
ms.technology: xml
ms.topic: conceptual
helpviewer_keywords: 
  - "FOR XML clause, nested FOR XML queries"
  - "queries [XML in SQL Server], nested FOR XML"
  - "nested FOR XML queries"
ms.assetid: 7604161a-a958-446d-b102-7dee432979d0
author: MightyPen
ms.author: genemi
---
# Use Nested FOR XML Queries
[!INCLUDE[appliesto-ss-asdb-xxxx-xxx-md](../../includes/appliesto-ss-asdb-xxxx-xxx-md.md)]
  The **xml** data type and the [TYPE directive in FOR XML queries](../../relational-databases/xml/type-directive-in-for-xml-queries.md) enable the XML returned by the FOR XML queries to be processed on the server as well as on the client.  
  
## 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.  
  
```  
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](../../t-sql/xml/value-method-xml-data-type.md).  
  
```  
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)](../../t-sql/xml/query-method-xml-data-type.md) 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. 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](../../relational-databases/xml/for-xml-query-compared-to-nested-for-xml-query.md) and [Shape XML with Nested FOR XML Queries](../../relational-databases/xml/shape-xml-with-nested-for-xml-queries.md).  
  
 You can generate XML hierarchies that include siblings by specifying nested AUTO mode FOR XML queries. For more information, see [Generate Siblings with a Nested AUTO Mode Query](../../relational-databases/xml/generate-siblings-with-a-nested-auto-mode-query.md).  
  
 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](../../relational-databases/xml/for-xml-query-compared-to-nested-for-xml-query.md)  
 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.  
  
 [Generate Siblings with a Nested AUTO Mode Query](../../relational-databases/xml/generate-siblings-with-a-nested-auto-mode-query.md)  
 Shows how to generate siblings by using a nested AUTO mode query  
  
 [Use Nested FOR XML Queries in ASP.NET](../../relational-databases/xml/use-nested-for-xml-queries-in-asp-net.md)  
 Demonstrates how an ASPX application can use FOR XML to return XML from [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].  
  
 [Shape XML with Nested FOR XML Queries](../../relational-databases/xml/shape-xml-with-nested-for-xml-queries.md)  
 Shows how to use nested FOR XML queries to control the structure of an XML document created by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)].