Home > Articles > Web Services > XML

  • Print
  • + Share This
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>
  • + Share This
  • 🔖 Save To Your Account