for clauses

A for clause iterates through the result of an expression and binds a variable to each item in the sequence.

The simplest type of for clause contains one variable and an associated expression. In the following example, the for clause includes a variable called $i and an expression that constructs the sequence (1, 2, 3):

SELECT XMLQUERY(
  'for $i in (1, 2, 3)
  return <output>{$i}</output>')
FROM SYSIBM.SYSDUMMY1

When the for clause is evaluated, three variable bindings are created (one binding for each item in the sequence):

$i = 1
$i = 2
$i = 3

The return clause in the example executes once for each binding. The SQL statement returns a column with the following row:

<output>1</output><output>2</output><output>3</output>

A for clause can contain multiple variables, each of which is bound to the result of an expression. In the following example, a for clause contains two variables, $a and $b, and expressions that construct the sequences 1 2 and

4
5

SELECT XMLQUERY(
  'for $a in (1, 2), $b in (4, 5)
  return <output>{$a, $b}</output>'
  COLUMNS "x" XML PATH '.')
  FROM SYSIBM.SYSDUMMY1

When the for clause is evaluated, a tuple of variable bindings is created for each combination of values. This results in four tuples of variable bindings:

($a = 1, $b = 4)
($a = 2, $b = 4)
($a = 1, $b = 5)
($a = 2, $b = 5)

The return clause in the example executes once for each tuple of bindings. The SQL statement returns a column with the following rows:

<output>1 4</output>
<output>2 4</output>
<output>1 5</output>
<output>2 5</output>

When the binding expression evaluates to an empty sequence, no for binding is generated, and no iteration is performed. In the following example, the binding sequence evaluates to an empty sequence and no iteration is performed. The node sequence in the return clause is not returned.

SELECT XMLQUERY(
  'for $node in 
  (<a test="b" />,
   <a test="c" />,
   <a test="d" />)[@test = "1"] 
  return 
  <test>
    Sample return response
  </test>')
  FROM SYSIBM.SYSDUMMY1

Positional variables in for clauses

Each variable that is bound in a for clause can have an associated positional variable that is bound at the same time. The name of the positional variable is preceded by the keyword at. When a variable iterates over the items in a sequence, the positional variable iterates over the integers that represent the positions of those items in the sequence, starting with 1. You can reference the positional variables in the same way that you reference any other variables.

In the following example, the for clause includes a variable called $cat and an expression that constructs the sequence ("Persian", "Calico", "Siamese"). The clause also includes the positional variable $i, which is referenced in an attribute constructor to compute the value of the order attribute:

SELECT XMLQUERY(
  'for $cat at $i in 
  ("Persian", "Calico","Siamese")
  return <cat order="{$i}">{$cat}</cat>')
  FROM SYSIBM.SYSDUMMY1

When the for clause is evaluated, three tuples of variable bindings are created, each of which includes a binding for the positional variable:

($i = 1, $cat = "Persian")
($i = 2, $cat = "Calico")
($i = 3, $cat = "Siamese")

The return clause in the example executes once for each tuple of bindings. The expression results in the following output:

<cat order="1">Persian</cat><cat order="2">Calico</cat><cat order="3">Siamese</cat>

Although each output element contains an order attribute, the actual order of the elements in the output stream is not guaranteed unless the FLWOR expression contains an order by clause such as order by $i. The positional variable represents the ordinal position of a value in the input sequence, not in the output sequence.