FLWOR Expressions

Similar to the T-SQL SELECT statement, XQuery FLWOR statements are the foundation for querying, filtering, and sorting results from an XML document. FLWOR stands for for, let, where, order by, and return and is pronounced "flower." Although simple XPath expressions are useful, the real power of XQuery shines through with FLWOR expressions.

Take a moment to think about a SQL SELECT clause. The main ingredients there are the SELECT, FROM, and WHERE clauses. The FROM clause specifies the tables to query. Then the WHERE clause is evaluated for each row in the FROM clause tables. Those rows that pass the evaluation have those fields that are specified in the SELECT clause output.

FLWOR statements are very similar to a SQL SELECT. As mentioned previously, it is made up of five parts, or clauses, which do the following:

• q for-Specifies the XML node list to iterate over, and is akin to the SELECT statement's FROM clause. The list of XML nodes is specified via an XPath expression. For example, if you wanted to iterate over all of the <Product> elements, you would use the XPath expression doc (“Products.xml”)/Products/Product.

• q let-Enables you to declare a variable and give it a value.

• q where-Contains an expression that evaluates to a Boolean, just like the WHERE clause in a SQL SELECT statement. Each XML node in the XML node list in the for clause is evaluated by the where clause expression. Those that evaluate to True move on; those that don't are passed over.

• q order by-Allows you to order the results of the query expression in ascending or descending order.

• q return-Specifies the content that is returned from the FLWOR expression.

In a later section, you get an in-depth look at each of these clauses in the FLWOR expression.

A Simple FLWOR Expression

The simplest XQuery FLWOR expression is something like this:

      for $p in $doc/Products/Product      return $p 

This simply returns all the Product elements in the document $doc. You can add a bit of substance with an XQuery where clause and a slightly more functional XQuery return clause, as follows:

      for $p in $doc/Products/Product      where $p/ProductID = 707      return $p/Name 

This now returns the product that is identified by product id 707.

If you know SQL, you will probably find this very similar to the corresponding SQL statement:

      SELECT p.Name FROM Product p WHERE p.ProductID = 707 

On the other hand, an equivalent query using XPath would be this:

      $doc/Products/Product[ProductID=707]/Name 

As you can see, you can produce the same output using XPath as well. So the question is how do you know which style to use and when? It depends on what you're used to. If you've been using XML for years, especially XML with the deep hierarchy found in narrative documents, you'll probably be comfortable with path expressions. But if you're more comfortable with the idea of representing your data as a table, then the FLWOR style might suit you better.

In fact, an XPath path expression is completely equivalent to the previous FLWOR expression, and it's a legal XQuery on its own. In fact, every legal XPath expression is also legal in XQuery. The first query in this section can in fact be written like this:

      $doc/Products/Product 

As you'll see, FLWOR expressions are a lot more powerful than path expressions when it comes to doing joins. But for simple queries, the capabilities overlap and you have a choice.

An In-Depth Look at FLWOR Expressions

As mentioned before, the name FLWOR comes from the five clauses that make up a FLWOR expression: for, let, where, order by, and return. The following sections take a detailed look at each of these clauses.

for Clause

The behavior of the for clause is fairly intuitive: it iterates over an input sequence and calculates some value for each item in that sequence, returning a sequence obtained by concatenating the results of these calculations. In simple cases, there's one output item for every input item. For example:

      for $i in (1 to 10)      return $i * $i 

This returns the sequence (1, 4, 9, 16, 25, 36, 49, 64, 81, 100).

In this example, the input items are simple numbers, and the output items are also simple numbers. Numbers are an example of what XQuery calls atomic values. Other examples are strings, dates, booleans, and URIs. The XQuery data model allows sequences to contain XML nodes as well as atomic values, and the for expression can work on either.

Here's an example that takes nodes as input, and produces numbers as output. It counts the number of product numbers listed for each product:

      for $p in //Products/Product      return count($p/ProductNumber) 

This returns the output (1,1,1,1).

A FLWOR expression is just an expression, and you can use it anywhere an expression is allowed-it doesn't have to be at the top level of the query. The avg() function computes the average of a sequence of numbers, so you can use that to find the average of a group of elements.

As you can see from the previous example, XQuery is a functional language that you can use to calculate a value by passing the result of one expression or function into another expression or function. Any expression can be nested inside any other, and the FLWOR expression is no exception.

If you're coming from SQL, your instinct was probably to try and do the averaging and rounding in the return clause. But the XQuery way is actually much more logical. The return clause calculates one value for each item in the input sequence, whereas the avg() function applies to the result of the FLWOR expression as a whole.

And you can get from one sequence of nodes to another sequence of nodes. The for clause really comes into its own when you have more than one of them in a FLWOR expression. You will be introduced to that when you start looking at joins later in the chapter.

let Clause

The XQuery let clause simply declares a variable and gives it a value. For example:

      let $ProductID := 707      let $productWithProductID707 := //Products/Product[ProductID = $ProductID]      return count($productWithProductID707) 

Hopefully, the meaning of that is fairly intuitive. In fact, in this example you can simply replace each variable reference by the expression that provides the expression's value. This means that the result is the same as if you used the following:

      count(//Products/Product[ProductID = 707]) 

In a for clause, the variable is bound to each item in the sequence in turn. In a let clause, the variable takes only one value. This can be a single item or a sequence, and a sequence can contain nodes, atomic values, or a mixture of the two.

In most cases, variables are used purely for convenience, to simplify the expressions and make the code more readable. If you need to use the same expression more than once, then declaring a variable is also a good hint to the query processor to only do the evaluation once. In a FLWOR expression, you can have any number of for clauses, and any number of let clauses, and they can be in any order.

There's an important thing to note about variables in XQuery: they can't be updated. This means you can't write something like let $x:= $x+1. This rule might seem very strange if you're expecting XQuery to behave in the same way as procedural languages like JavaScript. But XQuery isn't that kind of language-it's a declarative language and works at a higher level. There are no rules about the order in which different expressions are executed, and this means that constructs whose result would depend on order of execution (like variable assignment) are banned. This constraint is essential to give optimizers the chance to find execution strategies that can search vast databases in fractions of a second.

where Clause

The XQuery where clause in a FLWOR expression performs a very similar function to the WHERE clause in a SQL select statement: it specifies a condition to filter the items you are interested in. The where clause in a FLWOR expression is optional, but if it appears it must only appear once, after all the for and let clauses. Here's an example that restates one of the earlier queries, but this time using a where clause:

      for $product in //Products/Product      where $product/ProductID = 707        and $product/@Category = "Helmets"      return $product/Name 

This query returns the product with product id 707 with the category of “Helmets”.

This style of coding is something that SQL users tend to be very comfortable with: First, define all the tables you're interested in, and then specify a WHERE expression to define all the restriction conditions that select subsets of the rows in each table as well as the join conditions that show how the various tables are related.

order by Clause

The order by clause enables you to sort the values in the returned result set. The order by keyword accepts a sorting expression, which should return an atomic value. Optionally, you can also specify ascending or descending for the sort order. The default sort order is ascending.

To sort the products in ascending order of product id, use the following query:

      for $product in //Products/Product      order by $product/ProductID ascending      return $product/Name 

return Clause

Every XQuery FLWOR expression has a return clause, and it always comes last. It defines the items that are included in the result. Usually the XQuery return clause generates a single item each time it's evaluated. In general, the return clause can also produce a sequence. For example, you can do the following:

      for $product in //Products/Product[@Category="Socks"]      return $product/Name 

This selects all the names for products that belong to the category “Socks”. However, you can also wrapper the resultant nodes into a root element so that it easily wrappers around the Name elements:

      <ProductNames>        {for $product in //Products/Product[@Category="Socks"]          return $product/Name}      </ProductNames> 

Generally a FLWOR expression without element constructors can only produce flat lists of values or nodes, and that's not usually enough. You usually want to produce an XML document as the output of the query, and XML documents aren't flat.

As a result, instead of doing purely relational joins that generate a flat output, you want to construct hierarchic output using a number of nested FLWOR expressions. The return clause might seem like the least significant part of the FLWOR, but a misplaced return can make a big difference. It is recommended that you always align the F (for), L (let), W (where), O (order by), and R (return) clauses of a single FLWOR expression underneath each other, indenting any nested expressions, so that you can see what's going on.

FLWOR Expressions Versus XPath Expressions

Using a FLWOR expression to define the result sequence when you could express the same sequence using an XPath expression can be overkill in some cases because of the complexity of FLWOR expressions. As a general rule of thumb, you must establish the use cases where the use of a FLWOR expression is justified. The following list provides basic scenarios where the use of FLWOR expressions makes sense:

• q If you want to iterate over a sequence of values that are returned as a result of an expression: Use the for clause, which binds a variable to successive values of the result set. Examples are the construction of new elements within the scope of the for clause and the retention of duplicates.

• q If you want to filter the result sequence of the for clause based on a predicate that cannot be defined using simple XPath expressions: Use the where clause to eliminate unwanted values in the result set. For example:

        for $i in (1, 2, 3), $j in (3, 4, 5)      where $i < $j      return sum($i + $j) 

• q If you want to sort the result set based on a sorting expression: Use the order by clause to define the sort on the result set.

• q If you want to define the shape of the returned result set using the results obtained from the for clause: Use the return statement to perform the shaping of the result set.

If your requirement does not fall in any of these scenarios, you must carefully evaluate the use of FLWOR expressions.