Suppose you want to return a list of product names preceded by a sequence number. Your first approach might be to use a variable as a counter, as shown in Example 9-8. However, the results are not what you might expect. This is because the return clause for each iteration is evaluated in parallel rather than sequentially. This means that you cannot make changes to the value of a variable in one iteration, and expect it to affect the next iteration of the for clause. At the beginning of every iteration, the $count variable is equal to 0.

Query
let $count := 0
for $prod in doc("catalog.xml")//product[@dept = ("ACC", "WMN")]
let $count := $count + 1
return <p>{$count}. {data($prod/name)}</p>
Results
<p>1. Fleece Pullover</p>
<p>1. Floppy Sun Hat</p>
<p>1. Deluxe Travel Bag</p>

Another temptation might be to use the position function, as shown in Example 9-9. However, this will return the same results as the previous example. In XQuery, unlike in XSLT, the position function only has meaning inside a predicate in a path expression. In this case, the value assigned to $prod is no longer in the context of three product items; therefore, it has no relative sequence number within that sequence.

for $prod in doc("catalog.xml")//product[@dept = ("ACC", "WMN")]
return <p>{$prod/position( )}. {data($prod/name)}</p>

Luckily, FLWORs have a special syntax that enables you to define a positional variable in the for clause. This variable, which is followed by the keyword at, is bound to an integer representing the iteration number, as shown in Example 9-10.

Query
for $prod at $count in doc("catalog.xml")//product[@dept = ("ACC", "WMN")]
return <p>{$count}. {data($prod/name)}</p>
Results
<p>1. Fleece Pullover</p>
<p>2. Floppy Sun Hat</p>
<p>3. Deluxe Travel Bag</p>

However, the positional variable in the at clause does not always work. For example, suppose you wanted to use both where and order by clauses in your FLWOR. This interferes with the sequencing, as shown in Example 9-11. First, the numbers are not in ascending order. This is because the results are ordered after the positional number is evaluated. Another problem is that the sequence numbers are 2, 3, and 4 instead of 1, 2, and 3. This is because there are four product elements returned in the for clause, and the first one was eliminated by the where clause.

Query
for $prod at $count in doc("catalog.xml")//product
where $prod/@dept = ("ACC", "MEN")
order by $prod/name
return <p>{$count}. {data($prod/name)}</p>
Results
<p>4. Cotton Dress Shirt</p>
<p>3. Deluxe Travel Bag</p>
<p>2. Floppy Sun Hat</p>

One way to resolve this is by embedding a second FLWOR in the let clause, as shown in Example 9-12. This embedded FLWOR returns all the products sorted and filtered appropriately. Then, the for clause contained in the main FLWOR uses the positional variable on the sorted sequence.

Query
let $sortedProds := for $prod in doc("catalog.xml")//product
                    where $prod/@dept = "ACC" or $prod/@dept = "MEN"
                    order by $prod/name
                    return $prod
for $sortedProd at $count in $sortedProds
return <p>{$count}. {data($sortedProd/name)}</p>
Results
<p>1. Cotton Dress Shirt</p>
<p>2. Deluxe Travel Bag</p>
<p>3. Floppy Sun Hat</p>

Sometimes it is also useful to test whether an item is last in a sequence. Earlier in this chapter, we saw that the position function is not useful unless it is in a predicate. The same is true of the last function, which limits its usefulness when testing for the last item, for example, in a FLWOR.

Suppose you want to concatenate the names of the products together, separated by commas. At the end of the last product name, you want to specify a period instead of a comma.^[*] The best approach is to assign the number of items to a variable in a let clause, as shown in Example 9-13.

Query
<p>{ let $prods := doc("catalog.xml")//product
     let $numProds := count($prods)
     for $prod at $count in $prods
     return if ($count = $numProds)
            then concat($prod/name,".")
            else concat($prod/name,",")
}</p>
Results
<p>Fleece Pullover, Floppy Sun Hat, Deluxe Travel Bag, Cotton Dress Shirt.</p>

The $numProds variable is bound to the number of products. A positional variable, $count, is used to keep track of the iteration number. When the $count variable equals the $numProds variable, you have arrived at the last item in the sequence.

Another approach is to use the is operator to determine whether the current $prod element is the last one in the sequence. This query is shown in Example 9-14. In this case, it is not necessary to count the number of items or to use a positional variable. The results are the same as in Example 9-13.

<p>{ let $prods := doc("catalog.xml")//product
     for $prod in $prods
     return if ($prod is $prods[last( )])
            then concat($prod/name,".")
            else concat($prod/name,", ")
}</p>