The real power of XQuery lies in its so-called FLWOR expressions. FLWOR is the acronym for for, let, where, order by, and return. A FLWOR expression is actually a for each loop. You can use it to iterate through a sequence returned by an XPath expression. Although you typically iterate through a sequence of nodes, you can use FLWOR expressions to iterate through any sequence. You can limit the nodes to be processed with a predicate, sort the nodes, and format the returned XML. The parts of a FLWOR statement are:
– For With a for clause, you bind iterator variables to input sequences. Input sequences are either sequences of nodes or sequences of atomic values. You create atomic value sequences by using literals or functions.
– Let With the optional let clause, you assign a value to a variable for a specific iteration. The expression used for an assignment can return a sequence of nodes or a sequence of atomic values.
– Where With the optional where clause, you filter the iteration.
– Order by Using the order by clause, you can control the order in which the elements of the input sequence are processed. You control the order based on atomic values.
– Return The return clause is evaluated once per iteration, and the results are returned to the client in the iteration order. With this clause, you format the resulting XML.
Here is an example of usage of all FLWOR clauses.
DECLARE @x AS XML;
SET @x = N’
<!– Comment 111 –>
<!– Comment 222 –>
SELECT @x.query(‘for $i in CustomersOrders/Customer/Order
let $j := $i/orderdate
where $i/@orderid < 10900
order by ($j)
AS [Filtered, sorted and reformatted orders with let clause];
The query iterates, as you can see from the for clause, through all Order nodes using an iterator variable and returns those nodes. The name of the iterator variable must start with a dollar sign ($) in XQuery. The where clause limits the Order nodes processed to those with an orderid attribute smaller than 10900.
The expression passed to the order by clause must return values of a type compatible with the gt XQuery operator. As you’ll recall, the gt operator expects atomic values. The query orders the XML returned by the orderdate element. Although there is a single orderdate element per order, XQuery does not know this, and it considers orderdate to be a sequence, not an atomic value. The numeric predicate specifies the first orderdate element of an order as the value to order by. Without this numeric predicate, you would get an error.
The return clause shapes the XML returned. It converts the orderid attribute to an element by creating the element manually and extracting only the value of the attribute with the data() function. It returns the orderdate element as well, and wraps both in the Orderorderid-element element. Note the braces around the expressions that extract the value of the orderid element and the orderdate element. XQuery evaluates expressions in braces; without braces, everything would be treated as a string literal and returned as such.
The let clause assigns a name to the $i/orderdate expression. This expression repeats twice in the query, in the order by and the return clauses. To name the expression, you have to use a variable different from $i. XQuery inserts the expression every time the new variable is referenced. Here is the result of the query.