In previous several posts, I provided some examples querying XML data mainly using XPath Expressions. Today we'll learn about the
FLWOR expressions. The FLWOR expression is the core of the XQuery language. With FLWOR expressions, we can create more complex queries. Also we can sort the result set.
FLWOR(for, let, where, order by, return). Below is a brief description for these 5 clauses.
for: Contains variables to hold each value in the sequence. This set up iterations. Similar like, but not exactly the same,
foreach loop in C#. The key different is the iteration in the FLWOR expression is not processed in ordered.
let: contains variables to hold a sequence. The let clause perform the assignment of a sequence to a variable. Only evaluate once (no iteration).
where: filter result. Similar to WHERE clause in SQL. (optional)
order by: sort the sequence. Similar to ORDER BY in SQL. Order by clause requires a singleton or an empty sequence. (optional)
return: returns result. Similar to SELECT clause in SQL. The return clause is evaluated once for each iteration in the for clause. (required)
Note: along with the return clause, a FLWOR expression requires at least one for or one let clause.
Let's go over some examples. Since we're using SQL Server to query our XML data, we'll need to use the
query() method in SQL Server. XQuery is case sensitive.
-- let's assume that we have a table contain owners
-- and all the detail about the stores that they own.
USE TEMPDB;
GO
IF OBJECT_ID(N'#business', 'U') IS NOT NULL
BEGIN
DROP TABLE #business;
END
GO
CREATE TABLE #business (
OwnerId INT NOT NULL,
OwnerName VARCHAR(50) NOT NULL,
stores XML --note: this is an XML data type
);
GO
--generate some XML data
DECLARE @bookstore1 XML =
N'
<stores>
<bookstore specialty="novel" address_city="Seattle">
<book style="autobiography">
<title>Create a Vision</title>
<author>
<first-name>Joe</first-name>
<last-name>Smith</last-name>
</author>
<price>12</price>
</book>
<book style="autobiography">
<title>Face the Sun</title>
<author>
<first-name>Joe</first-name>
<last-name>Cantrell</last-name>
</author>
<price>20</price>
</book>
</bookstore>
<bookstore specialty="science" address_city="London">
<book style="textbook">
<title>XML Reference</title>
<author first-name="Mary" last-name="Jane" />
<price>55</price>
</book>
<book style="novel" id="myfave">
<title>Paths to Success</title>
<author>
<first-name>Toni</first-name>
<last-name>Wilson</last-name>
</author>
<price intl="Canada" exchange="0.7">6.50</price>
</book>
</bookstore>
</stores>';
DECLARE @bookstore2 XML =
N'
<stores>
<bookstore specialty="computing" address_city="Seattle">
<book style="textbook">
<title>Advanced Game Testing</title>
<author>
<first-name>Sam</first-name>
<last-name>Heng</last-name>
</author>
<price>20</price>
</book>
<book style="textbook">
<title>XML Basic</title>
<author>
<first-name>Najwa</first-name>
<last-name>Ebanks</last-name>
</author>
<price>30</price>
</book>
</bookstore>
</stores>';
--insert xml data to the table.
INSERT INTO #business(OwnerId, OwnerName, stores)
VALUES (1, 'Hai Ton', @bookstore1)
,(2, 'Phuong Tran', @bookstore2);
-- all books
SELECT OwnerName,
stores.query('for $book in stores/store/book
return $book') AS books
FROM #business;
-- all books
-- note the syntax for let clause. It uses := not =
SELECT OwnerName,
stores.query('let $book := stores/store/book
return $book') AS books
FROM #business;
-- all books
-- notice, in this XQuery, the return clause doesn't use anything from the let clause
SELECT OwnerName,
stores.query('let $i := something
return stores/store/book') AS books
FROM #business;
--this xquery result in an error.
--A FLWOR expression requires at least one for or one let clause
SELECT OwnerName,
stores.query('return stores/store/book') AS books
FROM #business;
Result:
Msg 2209, Level 16, State 1, Line 3
XQuery [#business.stores.query()]: Syntax error near 'return'
-- all books price
SELECT OwnerName,
stores.query('for $book in stores/store/book
let $price := $book/price
return $price') AS BooksPrice
FROM #business;
-- all books price with title
-- notice we can specify multiple variables for the let clause,
-- and use a pair of () when return multiple sequences
SELECT OwnerName,
stores.query('for $book in stores/store/book
let $price := $book/price
let $title := $book/title
return ($title, $price)') AS BooksPrice
FROM #business;
-- What if we want the output to be like this:
--
-- abc
-- 12
--
-- this query only return $title and $price as string, not the values.
SELECT OwnerName,
stores.query('for $book in stores/store/book
let $price := $book/price
let $title := $book/title
return ($title, $price)') AS BooksPrice
FROM #business;
--use the curly brace {} to get the variables' values.
--However, this query still doesn't provide the correct output.
SELECT OwnerName,
stores.query('for $book in stores/store/book
let $price := $book/price
let $title := $book/title
return ({$title}, {$price})') AS BooksPrice
FROM #business;
--this query should provide the result in the format below.
--
-- abc
-- 12
--
--notice, we removed the comma between $title and $price variable.
SELECT OwnerName,
stores.query('for $book in stores/store/book
let $price := $book/price
let $title := $book/title
return ({$title} {$price})') AS BooksPrice
FROM #business;
That's all for now. Next post we'll learn how to use the where and order by clause to filter and sort result.
Reference:
http://www.w3.org/TR/2014/REC-xquery-30-20140408/
http://msdn.microsoft.com/en-us/library/ms189075%28v=sql.110%29.aspx
http://technet.microsoft.com/en-us/library/ms345122%28v=sql.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms190945.aspx
http://shop.oreilly.com/product/9780596006341.do
