The for Clause

A for clause, whose syntax is shown in Figure 6-2, sets up an iteration that allows the rest of the FLWOR to be evaluated multiple times, once for each item in the sequence returned by the expression after the in keyword. This sequence, also known as the binding sequence, can evaluate to any sequence of zero, one, or more items. In the previous example, it was a sequence of product elements, but it could also be atomic values, attribute nodes, or indeed items of any kind, or a mixture of items. If the binding sequence is the empty sequence, the rest of the FLWOR is simply not evaluated (it iterates zero times).

Additional features of the for clause are described elsewhere in the book. The at clause, which allows for positional variables, is described in “Working with Positions and Sequence Numbers”. The as clause, which declares the type of the variable, is described in “Type Declarations in FLWORs”. The allowing empty keywords, which allow outer joins, are described in “Outer joins with allowing empty”.

Figure 6-2. Syntax of a for clause

The FLWOR expression with its for clause is similar to loops in procedural languages such as C. However, one key difference is that in XQuery, because it is a functional language, the iterations are considered to be in no particular order. They do not necessarily occur sequentially, one after the other. One manifestation of this is that you cannot keep variable counters that are incremented with each iteration, or continuously append to the end of a string variable with each iteration. “Working with Positions and Sequence Numbers” provides more information about simulating counters.

for $i in 1 to 3
return <oneEval>{$i}</oneEval>

<oneEval>1</oneEval>
<oneEval>2</oneEval>
<oneEval>3</oneEval>

for $i in reverse(1 to 3)

for $i in (1 to 100)[. mod 2 = 0]

for $i in (1, 2)
for $j in ("a", "b")
return <oneEval>i is {$i} and j is {$j}</oneEval>

<oneEval>i is 1 and j is a</oneEval>
<oneEval>i is 1 and j is b</oneEval>
<oneEval>i is 2 and j is a</oneEval>
<oneEval>i is 2 and j is b</oneEval>

for $i in (1, 2), $j in ("a", "b")
return <oneEval>i is {$i} and j is {$j}</oneEval>

The let Clause

A let clause is a convenient way to bind a variable to a value. Unlike a for clause, a let clause does not result in iteration; it binds the whole sequence to the variable rather than binding each item in turn. The let clause serves as a programmatic convenience that avoids repeating the same expression multiple times. With some implementations, it may improve performance, because the expression is evaluated only once instead of each time it is needed.

The syntax of a let clause is shown in Figure 6-3. (The as clause, which declares the type of the variable, is described in “Type Declarations in FLWORs”.)

Figure 6-3. Syntax of a let clause

To illustrate the difference between for and let clauses, compare Example 6-5 with Example 6-2.

let $i := (1 to 3)
return <oneEval>{$i}</oneEval>

<oneEval>1 2 3</oneEval>

The FLWOR with the let clause returns only a single oneEval element, because no iteration takes place and the return clause is evaluated only once.

One or more let clauses can be intermingled with one or more for clauses. Each of the let and for clauses may reference a variable bound in any previous clause. Example 6-6 shows such a FLWOR.

let $doc := doc("catalog.xml")
for $prod in $doc//product
let $prodDept := $prod/@dept
let $prodName := $prod/name
where $prodDept = "ACC" or $prodDept = "WMN"
return $prodName

As with for clauses, adjacent let clauses can be represented using a slightly shortened syntax that replaces the let keyword with a comma, as in:

let $prodDept := $prod/@dept, $prodName := $prod/name

Another handy use for the let clause is to perform several functions or operations in order. For example, suppose you want to take a string and replace all instances of at with @, replace all instances of dot with a period (.), and remove any remaining spaces. You could write the expression:

replace(replace(replace($myString, 'at', '@'), 'dot', '.'), ' ', '')

but that is difficult to read and debug, especially as more functions are added. An alternative is the expression:

let $myString2 := replace($myString, 'at', '@')
let $myString3 := replace($myString2, 'dot', '.')
let $myString4 := replace($myString3, ' ', '')
return $myString4

which makes the query clearer.

The where Clause

The where clause is used to specify criteria that filter the results of the FLWOR. Its syntax is shown in Figure 6-4.

Figure 6-4. Syntax of a where clause

The where clause can reference variables that were bound by a for or let clause. For example:

where $prodDept = "ACC" or $prodDept = "WMN"

references the $prodDept variable. In addition to expressing complex filters, the where clause is also very useful for joins.

A where clause can be composed of many expressions joined by and and or keywords, as shown in Example 6-7.

for $prod in doc("catalog.xml")//product
let $prodDept := $prod/@dept
where $prod/number > 100
      and starts-with($prod/name, "F")
      and exists($prod/colorChoices)
      and ($prodDept = "ACC" or $prodDept = "WMN")
return $prod

Starting in version 3.0, it is also possible to have multiple where clauses in the same FLWOR. In previous versions, only one was allowed.

Note that when using paths within the where clause, they need to start with an expression that sets the context. For example, it has to say $prod/number > 100 rather than just number > 100. Otherwise, the processor does not know where to look for the number child.

The effective boolean value of the where clause is calculated. This means that if the where clause evaluates to a Boolean value false, a zero-length string, the number 0 or NaN, or the empty sequence, it is considered false, and the return clause of the FLWOR is not evaluated for that iteration. If the effective boolean value is true, the return clause is evaluated. For example, you could use:

where $prod/name

which returns true if $prod has a name child, and false if it does not. As another example, you could use:

where $numProds

which returns true if $numProds is a numeric value that is not zero (and not NaN). However, these types of expressions are somewhat cryptic, and it is preferable to use clearer expressions, such as:

where exists($prod/name)
and $numProds > 0

The return Clause

The return clause consists of the return keyword followed by the single expression that is to be returned. It is evaluated once for each iteration, assuming the where clause evaluated to true. The result value of the entire FLWOR is a sequence of items returned by each evaluation of the return clause. For example, the value of the entire FLWOR:

for $i in (1 to 3)
return <oneEval>{$i}</oneEval>

is a sequence of three oneEval elements, one for each time the return clause was evaluated.

If more than one expression is to be included in the return clause, they can be combined in a sequence. For example, the FLWOR:

for $i in (1 to 3)
return (<one>{$i}</one>, <two>{$i}</two>)

returns a sequence of six elements, two for each time the return clause is evaluated. The parentheses and comma are used in the return clause to indicate that a sequence of the two elements should be returned. If no parentheses or comma were used, the two element constructor would not be considered part of the FLWOR.

The Scope of Variables

When a variable is bound in a for or let clause, it can be referenced anywhere in that FLWOR after the clause that binds it. This includes, for example, other subsequent let or for clauses, the where clause, or the return clause. It cannot be referenced in a preceding clause, and it cannot be referenced in the clause itself, as in:

let $count := 0
for $prod in doc("catalog.xml")//product
let $count := $count + 1

This does not raise an error, but it is actually declaring a new variable with the same name, so it will have unexpected results, as described in “Adding Sequence Numbers to Results”.

If you bind two variables with the same name with the same containing expression, such as two for or let clauses that are part of the same FLWOR, you may again get unexpected results. It will create two separate variables with the same name, where the second masks the first and makes it inaccessible within the scope of the second variable.

Binding Multiple Variables

You can bind multiple variables in a quantified expression by separating the clauses with commas. As with the for clauses of FLWORs, the result is that every combination of the items in the sequences is taken. Example 6-11 returns true because there is a combination of values (where $i is 3 and $j is 10) where the satisfies expression is true.

some $i in (1 to 3), $j in (10, 11)
  satisfies $j - $i = 7

Three-Way Joins

Joins can be extended to allow more than two sources to be joined together. For example, suppose that, along with catalog.xml and order.xml, you also want to join the prices.xml document, which contains current pricing information for each product.

The query shown in Example 6-15 joins the prices.xml document with the others to provide pricing information in the results. It uses two expressions in the where clause to implement the two joins.

for $item in doc("order.xml")//item,
    $prod in doc("catalog.xml")//product,
    $price in doc("prices.xml")//prod
where $item/@num = $prod/number and $prod/number = $price/@num
return <item num="{$item/@num}"
             name="{$prod/name}"
             price="{$price/price}"/>

<item num="557" name="Fleece Pullover" price="29.99"/>
<item num="563" name="Floppy Sun Hat" price="69.99"/>
<item num="443" name="Deluxe Travel Bag" price="39.99"/>
<item num="557" name="Fleece Pullover" price="29.99"/>

Outer Joins

The previous join examples in this section are known as inner joins; the results do not include items without matching products or products without matching items. Suppose you want to create a list of products and join it with the price information. Even if there is no price, you still want to include the product in the list. This is known in relational databases as an outer join.

The query in Example 6-16 performs an outer join. It uses two FLWORs, one embedded in the return clause of the other. The outer FLWOR returns the list of products, regardless of the availability of price information. The inner FLWOR selects the price, if it is available.

for $prod in doc("catalog.xml")//product
return <product number="{$prod/number}">{
                attribute price
                   {for $price in doc("prices.xml")//prod
                    where $prod/number = $price/@num
                    return $price/price}
  }</product>

<product number="557" price="29.99"/>
<product number="563" price="69.99"/>
<product number="443" price="39.99"/>
<product number="784" price=""/>

Product 784 doesn’t have a corresponding price in the prices.xml document, so the price attribute has an empty value for that product.

xquery version "3.0";
for $prod in doc("catalog.xml")//product
for $price allowing empty
    in doc("prices.xml")//prices/priceList/prod[@num = $prod/number]
return <product number="{$prod/number}" price="{$price/price}"/>