Microsoft® SQL Server® 2012 Bible

364

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

You have seen XQuery used throughout this chapter; for example, the query() method of

the XML data type methods implements a subset of the XQuery specifi cations.

The real muscle of XQuery comes with the FLWOR operation. FLWOR stands for

FOR LET WHERE ORDER BY and RETURN.

The following example is an example of a basic FLWOR query using for and return:

DECLARE @var xml

SET @var = '

<Item>

<ItemNumber>V001</ItemNumber>

<ItemNumber>A017</ItemNumber>

</Item>

'

SELECT @var.query('for $item in Item/ItemNumber return $item')

/*

<ItemNumber>V001</ItemNumber>

<ItemNumber>A017</ItemNumber>

*/

where and order by can be specifi ed to fi lter and sort the output:

DECLARE @var xml

SET @var = '

<Item>

<ItemNumber>1010</ItemNumber>

<ItemNumber>1009</ItemNumber>

<ItemNumber>1008</ItemNumber>

<ItemNumber>1007</ItemNumber>

<ItemNumber>1006</ItemNumber>

<ItemNumber>1005</ItemNumber>

<ItemNumber>1004</ItemNumber>

<ItemNumber>1003</ItemNumber>

<ItemNumber>1002</ItemNumber>

<ItemNumber>1001</ItemNumber>

</Item>

'

SELECT @var.query('

for $item in Item/ItemNumber

where $item[.>"1005"]

order by $item

return $item')

<ItemNumber>1006</ItemNumber>

<ItemNumber>1007</ItemNumber>

c14.indd 364c14.indd 364 7/30/2012 4:49:04 PM7/30/2012 4:49:04 PM

http://www.it-ebooks.info