XML - Pro SQL Server Internals - page 223

Assume that you want to achieve a result set that includes OrderId , CustomerId , ArticleId , Quantity , and
Price columns. The first approach uses the nodes() method to shred the OrderLineItems node, and it will access
CustomerId and OrderId from there using descendant axes. The second approach will use two nodes() methods: one
to shred the individual Order nodes and a second node to shred OrderLineItems from those nodes. The code needed
to accomplish this is shown in Listing 11-18.
Listing 11-18. Drill-down approach: Queries
select
LineItems.Item.value('../@OrderId','int') as [OrderId]
,LineItems.Item.value('../@OrderNum','varchar(32)') as [OrderNum]
,LineItems.Item.value('@ArticleId','int') as [ArticleId]
,LineItems.Item.value('@Quantity','int') as [Quantity]
,LineItems.Item.value('@Price','float') as [Price]
from
@X.nodes('/Orders/Order/OrderLineItem') as LineItems(Item)
select
Orders.Ord.value('@OrderId','int') as [OrderId]
,Orders.Ord.value('@OrderNum','varchar(32)') as [CustomerId]
,LineItems.Item.value('@ArticleId','int') as [ArticleId]
,LineItems.Item.value('@Quantity','int') as [Quantity]
,LineItems.Item.value('@Price','float') as [Price]
from
@X.nodes('/Orders/Order') as Orders(Ord) cross apply
Orders.Ord.nodes('OrderLineItem') as LineItems(Item)
Figure 11-12 shows the execution plans for the queries. Descendant axes introduce an additional pair of XML
readers in the execution plan, which significantly degrades the performance of queries.