Database Reference
In-Depth Information
■
the algebrarization stage is responsible for name resolution, type derivation, and binding and converting XML
operators into a relational operators tree that can be further used by the Query Optimizer.
Note
When XML indexes are present, SQL Server always retrieves the data from them. Otherwise, it uses table-valued
functions to shred the XML data into a relational format. In both cases, the database engine works with a relational
representation of the XML data while optimizing and executing the queries.
The XML data type in SQL Server supports five different methods. Four of them—
value
,
exist
,
query
, and
nodes
—can be used to access and transform the data. The last one,
modify
, uses XML DML to modify the data.
value() Method
The
value()
method returns a scalar value from the XML instance. XPath is an expression that defines the path to the
value, and it should statically represent the singleton by referencing the single element or attribute from the XML.
The code shown in Listing 11-7 provides examples of the singletons in untyped XML.
Listing 11-7.
XPath, referencing singletons in untyped XML
declare
@X xml =
'<Order OrderId="42" OrderTotal="49.96">
<Customer Id="123"/>
<OrderLineItems>
<OrderLineItem>
<ArticleId>250</ArticleId>
<Quantity>3</Quantity>
<Price>9.99</Price>
</OrderLineItem>
</OrderLineItems>
</Order>'
-- SUCCESS: Get @Id from the first customer from first order
select @X.value('/Order[1]/Customer[1]/@Id','int')
-- ERROR: Not a singleton - XML can include the information about multiple orders and/or customers
select @X.value('/Order/Customer/@Id','int')
-- SUCCESS: Get first ArticleId from the first order from the first line item
select @X.value('/Order[1]/OrderLineItems[1]/OrderLineItem[1]/ArticleId[1]','int')
-- ERROR: Not a singleton - SQL Server does not know that ArticleId is the element rather than
section
select @X.value('/Order[1]/OrderLineItems[1]/OrderLineItem[1]/ArticleId','int')
■
the XML Schema helps SQL Server detect if Xpath references the singleton without specifying indexes/ordinals
in the path expressions.
Note
