Systems Engineering and RDBMS

FLWOR – A Basic Introduction

Posted by decipherinfosys on April 13, 2009

We have blogged about the usage of XML in RDBMS before and have covered it for both Oracle as well as SQL Server.  We have also discussed the XML data type, the different methods for retrieving XML (query(), exist(), value() and nodes()) as well as modifying (modify()) the XML data type and have discussed XML indexes and their usage.  If you are new to XML in SQL Server (or even if you have experience with it), one great resource to learn XML in SQL Server is from MVP Jacob Sebastien’s XML tutorial series that you can access here:

http://blog.beyondrelational.com/2008/10/sql-server-xml-tutorials.html

And for Oracle, you can read up the articles at Oracle XML Technology Center:

http://www.oracle.com/technology/tech/xml/index.html

We have demonstrated before how to use XPath expressions in XQuery…today, we are going to cover FLWOR which stands for: For-Let-Where-Order By-Return and is pronounced as “flower”.  It is similar to querying relational data with T-SQL with the difference that this is applied to XML data and returns data in the form of a XML.   FLWOR expressions are very powerful so if you deal with XML a lot, you should spend some time learning FLWOR.  Let’s pick up a simple example and see how this works and how we can draw parallels to the SQL language.

Let’s use one of the same XML that we have used in the past – the one which represents the employees, their hiring date and their departments:

declare @x xml
select @x =
'<Dept>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Jack</emp_name>
<hire_date>2003-12-12T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>John</emp_name>
<hire_date>2007-03-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>10</dept_id>
<emp_name>Beth</emp_name>
<hire_date>2008-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date></hire_date>
</Dept_Emp>
</Dept>'

Now, before we start using the FLWOR expressions, take a look at this BOL entry to familiarize yourself with the different clauses of the FLWOR expression.  Let’s start applying the FLWOR expressions now:

select @x.query
('
for $e in //emp_name return $e
')

This will give us this output:

<emp_name>Jack</emp_name>
<emp_name>John</emp_name>
<emp_name>Beth</emp_name>
<emp_name>Mary</emp_name>
<emp_name>Allen</emp_name>
<emp_name>Diana</emp_name>
<emp_name>Ramesh</emp_name>

Which in SQL terms is the same as doing: Select emp_name from Emp_Master.

Now, let’s apply a filter criteria as well:

select @x.query
('
for $e in //Dept_Emp where $e/dept_id = 20 return $e
')

Here, we are getting all those employee records who belong to the department with a dept_id value of 20.  The data output would be:

<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</Dept_Emp>
<Dept_Emp>
<dept_id>20</dept_id>
<emp_name>Ramesh</emp_name>
<hire_date />
</Dept_Emp>

And the SQL equivalent for this would be:

Select Dept_ID, Emp_Name, Hire_Date
from dbo.Emp_Master as EM
Where Dept_ID = 20;

So far, we have looked at For, Where and Return.  Let and Order By are two clauses that we have not touched yet.  Let’s look at those as well and then we can see how to use Return to restrict the columns that are returned back to us.

select @x.query
('
for $e in //Dept_Emp where $e/dept_id = 20 order by $e/@hire_date
return <emp>
{($e/emp_name, $e/hire_date)}
</emp>
')

In the example above, we are ordering by hire_date and then we are using the return clause to select only emp_name and hire_date instead of getting back the Department information as well since we have already filtered on the dept_id value of 20 anyways.  Here is how the data looks like:

<emp>
<emp_name>Mary</emp_name>
<hire_date>1999-01-03T00:00:00</hire_date>
</emp>
<emp>
<emp_name>Allen</emp_name>
<hire_date>2000-05-11T00:00:00</hire_date>
</emp>
<emp>
<emp_name>Diana</emp_name>
<hire_date>2001-09-09T00:00:00</hire_date>
</emp>
<emp>
<emp_name>Ramesh</emp_name>
<hire_date />
</emp>

The SQL equivalent is:

Select Emp_Name, Hire_Date
from dbo.Emp_Master as EM
Where Dept_ID = 20
order by hire_date;

So, out of FLWOR, the only one that we have not looked at so far is “L” which stands for Let and is used to declare a variable and set it’s value.  Let clause is not supported in SQL Server 2005 but is supported in SQL Server 2008.   Here is an example:

select @x.query
('
let $i := 20
for $e in //Dept_Emp where $e/dept_id = $i order by $e/@hire_date
return <emp>
{($e/emp_name, $e/hire_date)}
</emp>
')

In the example above, we have declared a local variable and assigned a value to it and then have used it in the where clause.  The SQL equivalent is:

declare @i int
Select Emp_Name, Hire_Date
from dbo.Emp_Master as EM
Where Dept_ID = @i
order by hire_date;

So, some things to glean from the post from above:

  • Select statement in SQL is the same as Return in FLWOR
  • From statement in SQL is the same as For in FLWOR
  • Where statement in SQL is the same as Where in FLWOR
  • Order by Statement in SQL is the same as Order by in FLWOR
  • Declaration of a local variable is the same as Let in FLWOR

This was a very basic introduction to FLWOR.  In future posts, we will look at some more complex queries as well as the XQuery functions and comparison operators.  Below is a list of resources that we have found to be very useful for learning more about FLWOR expressions and their usage:

Resources:

  • An introduction to FLWOR by Dr. Michael Kay – here.
  • BOL information on FLWOR – here.
  • Introduction to XQuery – by Michael Coles – here and another post here.
  • The series of posts by MVP Jacob Sebastien – the link is given in the post above.  And the link for the Oracle XML Technology Center.
  • MVP Bob Beauchemin’s blog posts – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: