FLWOR statement and iteration (XQuery)

2025-09-29

Applies to: SQL Server

XQuery defines the FLWOR iteration syntax. FLWOR is the acronym for for, let, where, order by, and return.

A FLWOR statement is made up of the following parts:

One or more FOR clauses that bind one or more iterator variables to input sequences.

Input sequences can be other XQuery expressions such as XPath expressions. They're either sequences of nodes or sequences of atomic values. Atomic value sequences can be constructed using literals or constructor functions. Constructed XML nodes aren't allowed as input sequences in SQL Server.
An optional let clause. This clause assigns a value to the given variable for a specific iteration. The assigned expression can be an XQuery expression such as an XPath expression, and can return either a sequence of nodes or a sequence of atomic values. Atomic value sequences can be constructed by using literals or constructor functions. Constructed XML nodes aren't allowed as input sequences in SQL Server.
An iterator variable. This variable can have an optional type assertion by using the as keyword.
An optional where clause. This clause applies a filter predicate on the iteration.
An optional order by clause.
A return expression. The expression in the return clause constructs the result of the FLWOR statement.

For example, the following query iterates over the <Step> elements at the first manufacturing location and returns the string value of the <Step> nodes:

DECLARE @x AS XML;

SET @x = '<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >
<Location LocationID="L1" >
  <Step>Manu step 1 at Loc 1</Step>
  <Step>Manu step 2 at Loc 1</Step>
  <Step>Manu step 3 at Loc 1</Step>
</Location>
<Location LocationID="L2" >
  <Step>Manu step 1 at Loc 2</Step>
  <Step>Manu step 2 at Loc 2</Step>
  <Step>Manu step 3 at Loc 2</Step>
</Location>
</ManuInstructions>';

SELECT @x.query('
   for $step in /ManuInstructions/Location[1]/Step
   return string($step)
');

Here's the result:

Manu step 1 at Loc 1 Manu step 2 at Loc 1 Manu step 3 at Loc 1

The following query is similar to the previous one, except that it's specified against the Instructions column, a typed xml column, of the ProductModel table. The query iterates over all the manufacturing steps, <step> elements, at the first work center location for a specific product.

SELECT Instructions.query('
   declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $Step in //AWMI:root/AWMI:Location[1]/AWMI:step
      return
           string($Step)
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Note the following from the previous query:

The $Step is the iterator variable.
The path expression, //AWMI:root/AWMI:Location[1]/AWMI:step, generates the input sequence. This sequence is the sequence of the <step> element node children of the first <Location> element node.
The optional predicate clause, where, isn't used.
The return expression returns a string value from the <step> element.

The string function (XQuery) is used to retrieve the string value of the <step> node.

Here's the partial result:

Insert aluminum sheet MS-2341 into the T-85A framing tool.
Attach Trim Jig TJ-26 to the upper and lower right corners of
the aluminum sheet. ....

These are examples of other input sequences that are allowed:

DECLARE @x AS XML;

SET @x = '';

SELECT @x.query('
for $a in (1, 2, 3)
  return $a');
-- result = 1 2 3

DECLARE @x AS XML;

SET @x = '';

SELECT @x.query('
for $a in
   for $b in (1, 2, 3)
      return $b
return $a');
-- result = 1 2 3

DECLARE @x AS XML;

SET @x = '<ROOT><a>111</a></ROOT>';

SELECT @x.query('
  for $a in (xs:string( "test"), xs:double( "12" ), data(/ROOT/a ))
  return $a');
-- result test 12 111

In SQL Server, heterogeneous sequences aren't allowed. Specifically, sequences that contain a mixture of atomic values and nodes aren't allowed.

Iteration is frequently used together with the XML construction (XQuery) syntax in transforming XML formats, as shown in the next query.

In the AdventureWorks sample database, the manufacturing instructions stored in the Instructions column of the Production.ProductModel table have the following form:

<Location LocationID="10" LaborHours="1.2"
            SetupHours=".2" MachineHours=".1">
  <step>describes 1st manu step</step>
   <step>describes 2nd manu step</step>
   ...
</Location>
...

The following query constructs new XML that has the <Location> elements with the work center location attributes returned as child elements:

<Location>
   <LocationID>10</LocationID>
   <LaborHours>1.2</LaborHours>
   <SetupHours>.2</SetupHours>
   <MachineHours>.1</MachineHours>
</Location>
...

Here's the query:

SELECT Instructions.query('
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
        for $WC in /AWMI:root/AWMI:Location
        return
          <Location>
            <LocationID> { data($WC/@LocationID) } </LocationID>
            <LaborHours>   { data($WC/@LaborHours) }   </LaborHours>
            <SetupHours>   { data($WC/@SetupHours) }   </SetupHours>
            <MachineHours> { data($WC/@MachineHours) } </MachineHours>
          </Location>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Note the following considerations from the previous query:

The FLWOR statement retrieves a sequence of <Location> elements for a specific product.
The data function (XQuery) is used to extract the value of each attribute so they're added to the resulting XML as text nodes instead of as attributes.
The expression in the RETURN clause constructs the XML that you want.

This is a partial result:

<Location>
  <LocationID>10</LocationID>
  <LaborHours>2.5</LaborHours>
  <SetupHours>0.5</SetupHours>
  <MachineHours>3</MachineHours>
</Location>
<Location>
   ...
<Location>
...

Use the `let` clause

You can use the let clause to name repeating expressions that you can refer to by referring to the variable. The expression assigned to a let variable is inserted into the query every time the variable is referenced in the query. This means that the statement is executed as many times as the expression gets referenced.

In the AdventureWorks2022 database, the manufacturing instructions contain information about the tools required and the location where the tools are used. The following query uses the let clause to list the tools required to build a production model, and the locations where each tool is needed.

SELECT Instructions.query('
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
        for $T in //AWMI:tool
            let $L := //AWMI:Location[.//AWMI:tool[.=data($T)]]
        return
          <tool desc="{data($T)}" Locations="{data($L/@LocationID)}"/>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Use the `where` clause

You can use the where clause to filter results of an iteration. This is illustrated in this next example.

In the manufacturing of a bicycle, the manufacturing process goes through a series of work center locations. Each work center location defines a sequence of manufacturing steps. The following query retrieves only those work center locations that manufacture a bicycle model and have less than three manufacturing steps. That is, they have less than three <step> elements.

SELECT Instructions.query('
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /AWMI:root/AWMI:Location
      where count($WC/AWMI:step) < 3
      return
          <Location >
           { $WC/@LocationID }
          </Location>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Note the following in the previous query:

The where keyword uses the count() function to count the number of <step> child elements in each work center location.
The return expression constructs the XML that you want from the results of the iteration.

Here's the result:

<Location LocationID="30"/>

The result of the expression in the where clause is converted to a Boolean value by using the following rules, in the order specified. These are the same as the rules for predicates in path expressions, except that integers aren't allowed:

If the where expression returns an empty sequence, its effective Boolean value is False.
If the where expression returns one simple Boolean type value, that value is the effective Boolean value.
If the where expression returns a sequence that contains at least one node, the effective Boolean value is True.
Otherwise, it raises a static error.

Multiple variable binding in FLWOR

You can have a single FLWOR expression that binds multiple variables to input sequences. In the following example, the query is specified against an untyped xml variable. The FLOWR expression returns the first <Step> element child in each <Location> element.

DECLARE @x AS XML;

SET @x = '<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >
<Location LocationID="L1" >
  <Step>Manu step 1 at Loc 1</Step>
  <Step>Manu step 2 at Loc 1</Step>
  <Step>Manu step 3 at Loc 1</Step>
</Location>
<Location LocationID="L2" >
  <Step>Manu step 1 at Loc 2</Step>
  <Step>Manu step 2 at Loc 2</Step>
  <Step>Manu step 3 at Loc 2</Step>
</Location>
</ManuInstructions>';

SELECT @x.query('
   for $Loc in /ManuInstructions/Location,
       $FirstStep in $Loc/Step[1]
   return
       string($FirstStep)
');

Note the following from the previous query:

The for expression defines $Loc and $FirstStep variables.
The two expressions, /ManuInstructions/Location and $FirstStep in $Loc/Step[1], are correlated in that the values of $FirstStep depend on the values of $Loc.
The expression associated with $Loc generates a sequence of <Location> elements. For each <Location> element, $FirstStep generates a sequence of one <Step> element, a singleton.
$Loc is specified in the expression associated with the $FirstStep variable.

Here's the result:

Manu step 1 at Loc 1
Manu step 1 at Loc 2

The following query is similar, except that it's specified against the Instructions column, typed xml column, of the ProductModel table. XML construction (XQuery) is used to generate the XML that you want.

SELECT Instructions.query('
     declare default element namespace "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /root/Location,
            $S  in $WC/step
      return
          <Step LocationID= "{$WC/@LocationID }" >
            { $S/node() }
          </Step>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Note the following in the previous query:

The for clause defines two variables, $WC and $S. The expression associated with $WC generates a sequence of work center locations in the manufacturing of a bicycle product model. The path expression assigned to the $S variable generates a sequence of steps for each work center location sequence in the $WC.
The return statement constructs XML that has a <Step> element that contains the manufacturing step and the LocationID as its attribute.
The declare default element namespace is used in the XQuery prolog so that all the namespace declarations in the resulting XML appear at the top-level element. This makes the result more readable. For more information about default namespaces, see Handling Namespaces in XQuery.

Here's the partial result:

<Step xmlns=
    "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
  LocationID="10">
     Insert <material>aluminum sheet MS-2341</material> into the <tool>T-
     85A framing tool</tool>.
</Step>
...
<Step xmlns=
      "https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
    LocationID="20">
        Assemble all frame components following blueprint
        <blueprint>1299</blueprint>.
</Step>
...

Use the `order by` clause

Sorting in XQuery is performed by using the order by clause in the FLWOR expression. The sorting expressions passed to the order by clause must return values whose types are valid for the gt operator. Each sorting expression must result in a singleton a sequence with one item. By default, sorting is performed in ascending order. You can optionally specify ascending or descending order for each sorting expression.

Note

Sorting comparisons on string values performed by the XQuery implementation in SQL Server are always performed by using the binary Unicode codepoint collation.

The following query retrieves all the telephone numbers for a specific customer from the AdditionalContactInfo column. The results are sorted by telephone number.

USE AdventureWorks2022;
GO

SELECT AdditionalContactInfo.query('
   declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
   declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
   for $a in /aci:AdditionalContactInfo//act:telephoneNumber
   order by $a/act:number[1] descending
   return $a
') AS Result
FROM Person.Person
WHERE BusinessEntityID = 291;

The Atomization (XQuery) process retrieves the atomic value of the <number> elements before passing it to order by. You can write the expression by using the data() function, but that isn't required.

order by data($a/act:number[1]) descending

Here's the result:

<act:telephoneNumber xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
  <act:number>333-333-3334</act:number>
</act:telephoneNumber>
<act:telephoneNumber xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
  <act:number>333-333-3333</act:number>
</act:telephoneNumber>

Instead of declaring the namespaces in the query prolog, you can declare them by using WITH XMLNAMESPACES.

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS act, 'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS aci)
SELECT AdditionalContactInfo.query('
   for $a in /aci:AdditionalContactInfo//act:telephoneNumber
   order by $a/act:number[1] descending
   return $a
') AS Result
FROM Person.Person
WHERE BusinessEntityID = 291;

You can also sort by attribute value. For example, the following query retrieves the newly created <Location> elements that have the LocationID and LaborHours attributes sorted by the LaborHours attribute in descending order. As a result, the work center locations that have the maximum labor hours are returned first.

SELECT Instructions.query('
     declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
for $WC in /AWMI:root/AWMI:Location
order by $WC/@LaborHours descending
        return
          <Location>
             { $WC/@LocationID }
             { $WC/@LaborHours }
          </Location>
') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 7;

Here's the result:

<Location LocationID="60" LaborHours="4"/>
<Location LocationID="50" LaborHours="3"/>
<Location LocationID="10" LaborHours="2.5"/>
<Location LocationID="20" LaborHours="1.75"/>
<Location LocationID="30" LaborHours="1"/>
<Location LocationID="45" LaborHours=".5"/>

In the following query, the results are sorted by element name. The query retrieves the specifications of a specific product from the product catalog. The specifications are the children of the <Specifications> element.

SELECT CatalogDescription.query('
     declare namespace
 pd="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
      for $a in /pd:ProductDescription/pd:Specifications/*
     order by local-name($a)
      return $a
    ') AS Result
FROM Production.ProductModel
WHERE ProductModelID = 19;

Note the following from the previous query:

The /p1:ProductDescription/p1:Specifications/* expression returns element children of <Specifications>.
The order by (local-name($a)) expression sorts the sequence by the local part of the element name.

Here's the result:

<Color>Available in most colors</Color>
<Material>Aluminum Alloy</Material>
<ProductLine>Mountain bike</ProductLine>
<RiderExperience>Advanced to Professional riders</RiderExperience>
<Style>Unisex</Style>

Nodes in which the ordering expression returns empty are sorted to the start of the sequence, as shown in the following example:

DECLARE @x AS XML;

SET @x = '<root>
  <Person Name="A" />
  <Person />
  <Person Name="B" />
</root>
';

SELECT @x.query('
  for $person in //Person
  order by $person/@Name
  return   $person
');

Here's the result:

<Person />
<Person Name="A" />
<Person Name="B" />

You can specify multiple sorting criteria, as shown in the following example. The query in this example sorts <Employee> elements first by Title and then by Administrator attribute values.

DECLARE @x AS XML;

SET @x = '<root>
  <Employee ID="10" Title="Teacher"        Gender="M" />
  <Employee ID="15" Title="Teacher"  Gender="F" />
  <Employee ID="5" Title="Teacher"         Gender="M" />
  <Employee ID="11" Title="Teacher"        Gender="F" />
  <Employee ID="8" Title="Administrator"   Gender="M" />
  <Employee ID="4" Title="Administrator"   Gender="F" />
  <Employee ID="3" Title="Teacher"         Gender="F" />
  <Employee ID="125" Title="Administrator" Gender="F" /></root>';

SELECT @x.query('for $e in /root/Employee
order by $e/@Title ascending, $e/@Gender descending

  return
     $e
');

Here's the result:

<Employee ID="8" Title="Administrator" Gender="M" />
<Employee ID="4" Title="Administrator" Gender="F" />
<Employee ID="125" Title="Administrator" Gender="F" />
<Employee ID="10" Title="Teacher" Gender="M" />
<Employee ID="5" Title="Teacher" Gender="M" />
<Employee ID="11" Title="Teacher" Gender="F" />
<Employee ID="15" Title="Teacher" Gender="F" />
<Employee ID="3" Title="Teacher" Gender="F" />

Limitations

These are the limitations:

The sorting expressions must be homogeneously typed. This is statically checked.
Sorting empty sequences can't be controlled.
The empty least, empty greatest, and collation keywords on order by aren't supported

XQuery Expressions

Feedback

Was this page helpful?

Share via

FLWOR statement and iteration (XQuery)

Use the let clause

Use the where clause

Multiple variable binding in FLWOR

Use the order by clause

Limitations

Related content

Feedback

Additional resources

Use the `let` clause

Use the `where` clause

Use the `order by` clause