SQL Server: XML FLOWR Expression in SQL Server 2005 and 2008

In this article, we will see how to use FLWOR Expression designed for querying XML data in SQL Server 2005 and 2008.

In my previous article – ‘XML Basics in SQL Server 2005 and 2008’, we have seen how to perform basic XML operations in SQL Server 2005 and 2008. Now let’s see how to perform some advanced queries for fetching XML data using FLWOR expression. FLWOR stands for –

• For loop.
• Let clause. (note that this was not supported in SQL Server 2005. But now it is supported in SQL Server 2008.
• Where condition.
• Order by clause.
• Return.

With a FLWOR expression, we have a couple of functions which we will be using for querying the XML data as below –

1. query()
2. value()
3. exist()
4. modify()

So now let’s start by writing queries to use the above functions. I have already demonstrated how to insert XML data with schema in my previous article. I will be using the same table ‘CustomerProducts’.

Let’s query the table first –

SELECT * FROM CustomerProducts

Now first query the XML in the column using XPath –

SELECT CustomerProduct.query('
declare default element namespace "http://www.microsoft.com/schemas/northwind/products";
/Product/ProductName')
FROM CustomerProducts

 The output is shown below –



The second query will fetch just the value from the XML –

SELECT CustomerProduct.value('
    declare default element namespace "http://www.microsoft.com/schemas/northwind/products";
    (/Product/UnitPrice)[1]',
    'varchar(250)')
FROM CustomerProducts

The output is shown below –

Let’s rewrite the above query with a condition. The condition will be to fetch all unit prices whose value is greater than 15.

The output will remain the same as shown above.

Now insert some data to do a bit more advanced queries. I have inserted two more products having product ID 2 and 3, product name ABC and XYZ and Unit price 13 and 29.

Now rerun the above query and you will see the following output –

If you observe the above output, it returns ‘NULL’ value whenever the condition is not true. In our case, we have one product’s unit price as 13 which is less than 15.

So let’s try to eliminate the ‘NULL’ values from our result. There are multiple ways of eliminating ‘NULL’ values. Let’s see a  few of them. Write the following query –

SELECT CustomerProduct.value('
    declare default element namespace "http://www.microsoft.com/schemas/northwind/products";
    (/Product/UnitPrice)[1][.>15]',
    'varchar(250)')
FROM CustomerProducts
WHERE
CustomerProduct.value('
    declare default element namespace "http://www.microsoft.com/schemas/northwind/products";
    (/Product/UnitPrice)[1]','int')>15

 The output is shown below –



Now try another query which shows one more way to eliminate the ‘NULL’ values from the result, by using an EXIST() function –



The output will be same as shown above.

You can also use a subquery to eliminate the ‘NULL’ values as shown below –



The output will be same as shown above.

Use XML FLOWR in Nested XML Elements

We will see how to use the FLOWR expression in nested XML. Create a table which will have two columns as shown below –

CREATE TABLE CustomerOrders
(
    CustomerID INT PRIMARY KEY,
    CustomerOrderDetails XML
)

Insert the following row in the table we just created –

INSERT INTO CustomerOrders VALUES
(1,'
<Customer>
    <CustomerID>1200</CustomerID>
    <CustomerName>
        <FirstName>Pravinkumar</FirstName>
        <LastName>D.</LastName>
    </CustomerName>
    <Order>
        <OrderID>2332</OrderID>
        <OrderDate>12/Jun/2010</OrderDate>
        <ProductName>ABCD</ProductName>
        <Quantity>40</Quantity>
    </Order>   
    <Order>
        <OrderID>2432</OrderID>
        <OrderDate>12/Jun/2010</OrderDate>
        <ProductName>MAMO</ProductName>
        <Quantity>20</Quantity>
    </Order>   
</Customer>
')

If you observe the XML shown above, it is having deep nesting of XML elements. Let’s start querying the above XML using FLWOR Expression.

First we will simply return all the products purchased by Customer 1. The query is as shown below –

SELECT CustomerID, CustomerOrderDetails.query('
    for $customer in /Customer/Order
    return $customer/ProductName
    ')
FROM CustomerOrders WHERE CustomerID=1

The output is shown below –



In the above query, we are using the FOR and Return statement of FLWOR expression. Now let’s modify the above query with the WHERE condition as shown below –

SELECT CustomerID, CustomerOrderDetails.query('
    for $customer in /Customer/Order
    where data($customer/Quantity) > 25
    return $customer/ProductName
    ')
FROM CustomerOrders WHERE CustomerID=1

The output is as shown below –



Now write a query to transform the XML as shown below –



The output is shown below –



Here’s an OrderBy clause from FLWOR expression –



The output is shown below –



Now let’s try an example of the ‘Let’ clause from FLWOR expression –


The output is shown below –



Use the modify() function to update the value of XML as well as delete the value of XML as shown below –

UPDATE CustomerProducts SET CustomerProduct.modify('declare default element namespace "http://www.microsoft.com/schemas/northwind/products"; replace value of (Product/UnitsInStock)[1]
    with 10') WHERE CustomerID=1000

Finally, try deleting the ‘LastName’ element from the CustomerOrders as shown below –

UPDATE CustomerOrders SET customerorderdetails.modify('delete (/Customer/CustomerName/LastName)') WHERE CustomerID=1