Database Reference
In-Depth Information
Listing 11-14.
The query() method
declare
@X xml =
N'<Order OrderId="42" OrderTotal="49.96">
<CustomerId>123</CustomerId>
<OrderNum>10025</OrderNum>
</Order>'
select
@X.query('/Order/CustomerId') as [Part of XML]
,@X.query('<Customer Id="{/Order/CustomerId/text()}"/>') as [Transform]
Figure 11-10.
The query() method
nodes() Method
The
nodes()
method shreds XML into relational data. It returns the rowset with rows representing the nodes
identified by the path expression. Furthermore, you can use other XML methods,
value()
, for example, to shred those
rows to individual elements and attributes.
The code shown in Listing 11-15 shows how you can access the individual nodes from the rowset and shred them
into individual values. You can see the results in Figure
11-11
.
Listing 11-15.
The nodes() method
declare
@X xml =
'<Order OrderId="42" OrderTotal="49.96">
<CustomerId>123</CustomerId>
<OrderNum>10025</OrderNum>
<OrderDate>2013-07-15T10:05:20</OrderDate>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
<OrderLineItem>
<ArticleId>404</ArticleId>
<Quantity>1</Quantity>
<Price>19.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'
