SQL Server knowledge center

FLWOR in XQuery

By : Kasim Wirama, MCDBA, MCITP

Like relational query statement, XQuery in SQL Server introduces FLWOR construct. FLWOR stands for For, Let, Where, Order by, Return. You can associate SELECT query statement with Return statement.

For example: to return content of Specifications element on CatalogDescription column at Production.ProductModel table, Adventureworks database, you can issue FLWOR statement here :

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS ns)
SELECT CatalogDescription.query(N'for $spec in //ns:ProductDescription/ns:Specifications/*
return $spec/text()') AS ModelDescription
FROM Production.ProductModel
WHERE ProductModelID = 19;

It returns the following result :

Almuminum AlloyAvailable in most colorsMountain bikeUnisexAdvanced to Professional riders

With XML dynamic construction (wrapped with { }), you could manipulate query result as XML format as the following query sample shown :

WITH xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS ns)
SELECT  catalogdescription.query('<root> { for $item in //*:Specifications/*
                return <detail>{ $item/text() }</detail> } </root>') AS [DESC]
FROM production.productmodel
WHERE productmodelid = 19;

The result is :

<root>
  <detail>Almuminum Alloy</detail>
  <detail>Available in most colors</detail>
  <detail>Mountain bike</detail>
  <detail>Unisex</detail>
  <detail>Advanced to Professional riders</detail>
</root>

You can specify for…in… construction more than one separated by comma (,), here is the example to do Cartesian operation between nodes under wm:Warranty and nodes under p1:Specifications.

WITH xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS ns)
SELECT CatalogDescription.query(N'<root> {
for $specItem in //*:Specifications/*,
$featItem in //*:Warranty/node()
return <detail> { $specItem/text() } + { $featItem/. cast as xs:string? } </detail> } </root>')
AS [DESC]
FROM Production.ProductModel
WHERE ProductModelID = 19;

The result is :

<root>
  <detail>Almuminum Alloy + 3 years</detail>
  <detail>Almuminum Alloy + parts and labor</detail>
  <detail>Available in most colors + 3 years</detail>
  <detail>Available in most colors + parts and labor</detail>
  <detail>Mountain bike + 3 years</detail>
  <detail>Mountain bike + parts and labor</detail>
  <detail>Unisex + 3 years</detail>
  <detail>Unisex + parts and labor</detail>
  <detail>Advanced to Professional riders + 3 years</detail>
  <detail>Advanced to Professional riders + parts and labor</detail>
</root>

You can use bind variable inherited from for…in… construct, you need to output XML value for color element under Specifications element, so the XQuery statement using bind value ($colorItem) is :

WITH xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS ns)
SELECT CatalogDescription.query(N'<root> {
for $specItem in //*:Specifications,
$colorItem in $specItem/Color
return <ItemColor> { $colorItem/text() } </ItemColor> } </root>') AS Color
FROM Production.ProductModel
WHERE ProductModelID = 19;

The result is :

Share this post: | | | |