␡
- The EXPLICIT Mode of FOR XML
- More than One Child Tag
- Choosing the Columns for the ORDER BY Clause
- Parent Tag Not Open Error
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
More than One Child Tag
The query we just examined earlier was fairly simple – it had three nodes and each node was the parent of the following node. What happens if we have multiple child nodes with the same parent? Simply specify which node needs to be the parent for each of the child nodes. The following example brings back <Orders> and <OrderDetails> tags both as children of the <Customers> tag:
SELECT 1 AS TAG, NULL AS PARENT, Customers.CustomerID AS [Customers!1!CustomerID], Customers.CompanyName AS [Customers!1!CompanyName], NULL AS [Orders!2!OrderID], NULL AS [Orders!2!OrderDate], NULL AS [Orders!2!ShipCity], NULL AS [OrderDetails!3!Quantity], NULL AS [Products!4!ProductName] FROM Customers INNER JOIN Orders ON Customers.CustomerID = ORders.CustomerID INNER JOIN [Order Details] OD ON od.OrderID = Orders.OrderID INNER JOIN Products ON Products.ProductID = OD.ProductID WHERE Products.ProductID = 9 UNION ALL SELECT 2 AS TAG, 1 AS PARENT, Customers.CustomerID AS [Customers!1!CustomerID], NULL AS [Customers!1!CompanyName], Orders.OrderID AS [Orders!2!OrderID], Orders.OrderDate AS [Orders!2!OrderDate], Orders.ShipCity AS [Orders!2!ShipCity], NULL AS [OrderDetails!3!ProductID], NULL AS [OrderDetails!3!Quantity], NULL AS [Products!4!ProductName] FROM Customers INNER JOIN Orders ON customers.customerID = Orders.CustomerID INNER JOIN [Order Details] OD ON od.OrderID = Orders.OrderID INNER JOIN Products ON Products.ProductID = OD.ProductID WHERE Products.ProductID = 9 UNION ALL SELECT 3 AS TAG, 1 AS PARENT, Customers.CustomerID AS [Customers!1!CustomerID], NULL AS [Customers!1!CompanyName], Orders.OrderID AS [Orders!2!OrderID], NULL AS [Orders!2!OrderDate], NULL AS [Orders!2!ShipCity], OD.ProductID AS [OrderDetails!3!ProductID], OD.Quantity AS [OrderDetails!3!Quantity], NULL AS [Products!4!ProductName] FROM Customers INNER JOIN Orders ON customers.customerID = Orders.CustomerID INNER JOIN [Order Details] OD ON OD.OrderID = Orders.OrderID INNER JOIN Products ON Products.ProductID = OD.ProductID WHERE Products.ProductID = 9 ORDER BY [Customers!1!CustomerID], [Orders!2!OrderID] FOR XML EXPLICIT
Abbreviated Results:
<Customers CustomerID="CONSH" CompanyName="Consolidated Holdings">
<Orders OrderID="10848" OrderDate="1998-01-23T00:00:00" ShipCity="London"/>
<OrderDetails ProductID="9" Quantity="3"/>
</Customers>
<Customers CustomerID="HUNGO" CompanyName="Hungry Owl All-Night Grocers">
<Orders OrderID="10687" OrderDate="1997-09-30T00:00:00" ShipCity="Cork"/>
<OrderDetails ProductID="9" Quantity="50"/>
</Customers>
Notice that I specified tag 1 as the parent for both tag 2 and tag 3. Had I specified tag 2 as the parent of tag 3, the results would look a bit different:
<Customers CustomerID="CONSH" CompanyName="Consolidated Holdings">
<Orders OrderID="10848" OrderDate="1998-01-23T00:00:00" ShipCity="London">
<OrderDetails ProductID="9" Quantity="3"/>
</Orders>
</Customers>
<Customers CustomerID="HUNGO" CompanyName="Hungry Owl All-Night Grocers">
<Orders OrderID="10687" OrderDate="1997-09-30T00:00:00" ShipCity="Cork">
<OrderDetails ProductID="9" Quantity="50"/>
</Orders>
</Customers>