Database Reference
In-Depth Information
select
t.c.query('.') as [Raw Node]
,t.c.value('(ArticleId/text())[1]','int') as [ArticleId]
from @X.nodes('/Order/OrderLineItems/OrderLineItem') as t(c)
Figure 11-11.
The nodes() method
When you use the
nodes()
method with the XML column from the table, you must use the
APPLY
operator.
You can see an example of this in Listing 11-16.
Listing 11-16.
Using the nodes() method with the APPLY operator
select
t.ID
,sum(Items.Item.value('(Quantity/text())[1]','int') *
Items.Item.value('(Price/text())[1]','float')) as [Total]
from
dbo.ElementCentricUntyped t cross apply
t.XMLData.nodes('/Order/OrderLineItems/OrderLineItem')
as Items(Item)
group by
t.ID
You must avoid referencing parent nodes with descendant axes in the path expressions; rather you should use
drill-down approach with multiple
nodes()
methods instead.
Now let's compare the two approaches. Assume that you have the XML that contains information about multiple
orders, as shown in Listing 11-17.
Listing 11-17.
Drill-down approach: XML
declare
@X xml =
N'<Orders>
<Order OrderId="42" CustomerId="123" OrderNum="10025">
<OrderLineItem ArticleId="250" Quantity="3" Price="9.99"/>
<OrderLineItem ArticleId="404" Quantity="1" Price="19.99"/>
</Order>
<Order OrderId="54" CustomerId="234" OrderNum="10025">
<OrderLineItem ArticleId="15" Quantity="1" Price="14.99"/>
<OrderLineItem ArticleId="121" Quantity="2" Price="6.99"/>
</Order>
</Orders>'
