XML XQUERY FLWOR Expressions in SQL Server

The real power of XML in SQL Server lies in its so-called xquery FLWOR expressions. I personally love to play with XML in SQL Server because it’s so powerful and easy to integrate. FLWOR is the acronym for for, let, where, order by and return. A FLWOR expression is actually a for each loop which typically iterates through a sequence of nodes.

  • for: Binds the iterator variables to input sequences.
  • let: It’s an optional clause, we can assign a value to a variable for a specific iteration.
  • where: It’s an optional clause to filter the iteration.
  • order by: To control the order in which the elements of the input sequence are processed.
  • return: It gets evaluated once per iteration, and the results are returned back in the iteration order. This clause is mainly used to format the result of XML.

Note: XQuery is case sensitive.

xml-flowr-xquery-sql-server
FOR and RETURN:

Note: for and return clauses are minimum required clauses for a FLOWR expression. for clause iterates through the given nodes of XML keeping the loop sequence in $i variable.

Result: All book details in the output format as shown in the above image.

LET and RETURN:

let (optional) clause allows us to declare one or more variable in the xquery to keep value in it during the iteration so that it can be used inside the xquery. Inshort declares a variable and gives it a value.

Result: Same result as above but one thing to notice here is that Genre attribute value is set in a variable during the iteration and later used to form the xml in return clause.

WHERE and RETURN:

where (optional) clause is self descriptive, it is used to filter the values. It can be applied against a variable or direct element/attribute value to evaluate the where condition.

Result: Only the book details that are matched with genre Fantasy.

ORDER BY and RETURN:

order by  (optional) clause, simple orders/sorts the output.

Result: Only the book details that are matched with genre Fantasy and ordered by PublishDate .

RETURN:

It is an mandatory clause in xquery FLWOR expression. Usually the XQuery return clause generates a single item each time it’s evaluated. In short tt defines the items that are included in the result. We can customize the XML output in the return clause. All above query has return so I am not providing any examples on this.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: