Microsoft® SQL Server® 2012 Bible

354

Part III: Advanced T-SQL Data Types and Querying Techniques

XQuery expression and evaluates the expression to a single node. During the process the

results are cast to the specifi ed SQL Server data type and returned. The following example

illustrates this using an XML variable.

DECLARE @var xml

SET @var = '<Order OrderID="1">

<Item>

<ItemNumber>V001</ItemNumber>

<Quantity>1</Quantity>

<Price>299.99</Price>

</Item>

</Order>'

SELECT @var.value('(Order/@OrderID)[1]', 'INT') as orderid,

@var.value('(Order/Item/ItemNumber)[1]', 'NVARCHAR(50)') as itemnumber

The following example illustrates the same functionality but directly queries an

XML data type column.

SELECT ItemData.value('(Order/@OrderID)[1]', 'INT') as orderid,

ItemData.value('(Order/Item/ItemNumber)[1]', 'NVARCHAR(50)') as itemnumber

FROM ItemInfo

nodes()

Suppose you want to return the values from nodes that match a specifi c expression.

The nodes() method returns a rowset representation of the XML document. An XQuery

operation can be performed on each node returned by the nodes() method.

DECLARE @var xml

SET @var = '

<Item>

<ItemNumber>V001</ItemNumber>

<ItemNumber>A017</ItemNumber>

<ItemNumber>P002</ItemNumber>

</Item>'

SELECT var.value('.','NVARCHAR(10)') as ItemNumber

FROM @var.nodes('/Item/ItemNumber') o(var)

The following example illustrates the same functionality but directly queries an XML data

type column.

SELECT OrderID,

ItemData.value('(Order/Item/ItemNumber)[1]', 'NVARCHAR(50)') as itemnumber

FROM ItemInfo

CROSS APPLY ItemData.nodes('/Order/Item') o(x)

c14.indd 354c14.indd 354 7/30/2012 4:49:02 PM7/30/2012 4:49:02 PM

http://www.it-ebooks.info