Microsoft SQL Server 2012 Bible

XQuery and FLWOR Operations

XQuery is a language that provides the ability to query structured and semi-structured XML data. XQuery is based on the existing XPath query language but includes added support for improved iteration, sorting results, and XML construction.

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 specifications.

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 specified to filter 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> <ItemNumber>1008</ItemNumber> <ItemNumber>1009</ItemNumber> <ItemNumber>1010</ItemNumber> ...