SQL Server: How to show master & detail data from xml

Question

Here is a sample xml
<Data>
<child>
<name>Tim</name>
<wishlist>
<article>
<artno>21491269</artno>
<description>Crane</description>
<price>12.50</price>
</article>
<article>
<artno>21499517</artno>
<description>Keyboard</description>
<price>10</price>
</article>
<article>
<artno>21521591</artno>
<description>Crime Investigation Game</description>
<price>9.95</price>
</article>
</wishlist>
</child>
<child>
<name>Josh</name>
<wishlist>
<article>
<artno>3491269</artno>
<description>Zeep</description>
<price>10.50</price>
</article>
<article>
<artno>21499518</artno>
<description>Keyboard</description>
<price>50</price>
</article>
<article>
<artno>91521591</artno>
<description>Game</description>
<price>19.95</price>
</article>
</wishlist>
</child>
</Data>

please guide how to show this master details data using xquery?

Thanks

Answer 1

It's not clear what result you want, but here is a sample that I have lying around that may get you started:

DECLARE @x xml = 
'<Orders>
    <Order>
       <OrderID>13000</OrderID>
       <CustomerID>ALFKI</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>2</EmployeeID>
       <OrderDetails>
           <ProductID>76</ProductID>
           <Price>123</Price>
           <Qty>10</Qty>
       </OrderDetails>
       <OrderDetails>
           <ProductID>16</ProductID>
           <Price>3.23</Price>
           <Qty>20</Qty>
       </OrderDetails>
    </Order>
    <Order>
       <OrderID>13001</OrderID>
       <CustomerID>VINET</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>1</EmployeeID>
       <OrderDetails>
           <ProductID>12</ProductID>
           <Price>12.23</Price>
           <Qty>1</Qty>
       </OrderDetails>
    </Order>
</Orders>'


SELECT OrderID    = T.Item.value('(OrderID/text())[1]', 'int'),
       CustomerID = T.Item.value('(CustomerID/text())[1]', 'nchar(5)'),
       OrderDate  = T.Item.value('(OrderDate/text())[1]',  'datetime'),
       EmployeeId = T.Item.value('(EmployeeID/text())[1]', 'smallint')
FROM   @x.nodes('Orders/Order') AS T(Item)

SELECT OrderID    = O.n.value('(OrderID/text())[1]',   'int'),
       ProductID  = D.n.value('(ProductID/text())[1]', 'int'),
       Price      = D.n.value('(Price/text())[1]',     'decimal(10,2)'),
       Qty        = D.n.value('(Qty/text())[1]',       'int')
FROM   @x.nodes('/Orders/Order') AS O(n)
CROSS APPLY O.n.nodes('OrderDetails') AS D(n)