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.
Example
This query constructs XML that provides sales order information. This includes the following:
Sales order header information, SalesOrderID, SalesPersonID, and OrderDate. AdventureWorks2008R2 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 two distinct queries that follow generate XML with a small difference in shape.
The first query generates XML in which <SalesOrderHeader> appears as a sibling childof <SalesOrder>:
USE AdventureWorks2008R2;
GO
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)
FROM (SELECT SalesOrderHeader.SalesOrderID, SalesOrderHeader.SalesPersonID
FROM Sales.SalesOrderHeader, Sales.SalesPerson
WHERE SalesOrderHeader.SalesPersonID = SalesPerson.BusinessEntityID
) 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="29825">
<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>
</SalesOrder>
<SalesOrder>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="29672">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
</Sales.SalesOrderHeader>
</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:
USE AdventureWorks2008R2;
GO
SELECT 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 SalesOrderID=43659 OR SalesOrderID=43660
FOR XML AUTO, TYPE;
This is the result:
<Sales.SalesOrderHeader SalesOrderID="43659" SalesPersonID="279" CustomerID="29825">
<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>
<Sales.SalesOrderHeader SalesOrderID="43660" SalesPersonID="279" CustomerID="29672">
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="762" OrderQty="1" UnitPrice="419.4589" />
<Sales.SalesOrderDetail SalesOrderID="43660" ProductID="758" OrderQty="1" UnitPrice="874.7940" />
</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) FROM Sales.SalesOrderHeader WHERE SalesOrderID='43659' or SalesOrderID='43660' FOR XML AUTO, TYPE ) as T(XmlCol);