Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

XQuery for the SQL Server DBA: Iteration and FLWOR

Let’s face it.  XQuery is at odds with almost everything a relational database holds dear.  Working with both at the same time can make the DBAs mind a blur of mixed messages.  Often, it just feels wrong.

XML Vs SQL

Of course my data is structured;
is there any other kind?
You can put values in an attribute, element, nested, nowhere, everywhere; it’s all cool man.
To a relational set,
order does not matter.
If you think order is important,
then by-golly I do too.
I prefer to work exclusively in sets. I like sets, or not; whatever.
Looping?? Only if the architect
has made a mistake – shameful.
Looping!!  I gave it an acronym;
say it with me… “FLWOR”.

XML is flexible, but the tradeoff is often you must go digging for your data.  Much of this digging I covered in my XPath post.  XPath is good for digging but you might have formatting, complex filtering, or order requirements that differ from your source XML.  Enter FLWOR and XQuery’s support for looping.

For this tutorial we’ll return to my original fantasy baseball XML doc.

DECLARE @x XML
SET @x = '<fantasy>
  <player Position="3B">
    <name>Evan Longoria</name>
    <team>Rays</team>
    <slg>512</slg>
  </player>
  <player Position="OF">
    <name>Carolos Gonzales</name>
    <team>Rockies</team>
    <slg>530</slg>
  </player>
  <player Position="C">
    <name>Buster Posey</name>
    <team>Giants</team>
    <slg>486</slg>
  </player>
  <player Position="OF">
    <name>Desmond Jennings</name>
    <team>Rays</team>
    <slg>409</slg>
  </player>
</fantasy>';

If you’ve been reading my other tutorials on XQuery this document should be familiar.  Let’s say this time our requirement for output is as follows:

  • Only players from the Tampa Bay Rays
  • Player name as an attribute
  • Slugging node spelled out, not abbreviated
  • In order of slugging percentage

To meet each of these requirements our best option is XQuery and FLWOR iteration.  I use caps below for formatting purposes – don’t forget however that XQuery is case sensitive.

FLWOR

You probably know this, but the acronym flwor stands for for, let, where, order by, return.  Sounds complex but it’s just a for loop.  The other clauses just make up the structure and loop options.

If you have any kind of experience with coding at all you should be familiar with for loops.  They allow you to iterate through a set of values applying logic to each value individually as you go.

FOR and RETURN

To make a valid flwor expression the only two required clauses are for and return.  Everything else is optional.

The for clause uses a variable identified by $ and an XPath statement to identify a sequence the loop will iterate through.  The return clause instructs XQuery on how to format the output.  It does this by providing an optional xml structure, variables and any additional XPath within braces { }.  It’s easy to get thrown off by the braces.  It’s kind of like dynamic XPath inside of FLWOR inside of XQuery inside of TSQL – multi leveled fun – no wonder no one enjoys this stuff.

SELECT @x.query('

   for $p in fantasy/player
   return <slugging>
            {($p/slg/text())[1]}
          </slugging>

')

This statement gets me all players with slugging spelled out but it’s missing their names.  It also does not filter teams to only Tampa Bay Rays, nor does it return in order of slugging percentage.

LET and WHERE

Since xml is semi-structured you can’t always rely on predicates for filtering (and navigating by axis would just get ridiculous).  So XQuery adds let and where.

The let clause allows you to declare one, (or more) additional variables that you can use within the XQuery statement.  The where clause provides the ability to filter by any one of the variables defined.

SELECT @x.query('

   for $p in fantasy/player
   let $t := $p/team
   where $t="Rays"
   return <slugging>
             {($p/slg/text())[1]}
          </slugging>

')

Don’t forget you can specify more than one let clause, and/or multiple conditions on the where.

SELECT @x.query('

   for $p in fantasy/player
   let $t := $p/team
   let $s := $p/slg
   where $t = "Rays" and $s > 500
   return <slugging>{($p/slg/text())[1]}</slugging>

')

If you want to get really crazy you can also nest multiple for loops within each other.

ORDER BY

Really the only clause left is order by. Pretty straightforward. Works basically the same as it’s TSQL counterpart.  You can also specify ascending or descending options with ascending being the default if neither is mentioned. You’ll notice the trusty ol’ predicate of [1] to satisfy the engine that atomic values are being returned. I also added the player name as an attribute of the player element.

SELECT @x.query('

   for $p in fantasy/player
   let $t := $p/team
   where $t="Rays"
   order by ($p/slg)[1] 
   return <player name="{ $p/name/text() }">
             <slugging>
                 { ($p/slg/text())[1] }
             </slugging>
          </player>

')

The result of this query is:

<player name="Desmond Jennings">
  <slugging>409</slugging>
</player>
<player name="Evan Longoria">
  <slugging>512</slugging>
</player>

Well, there you have it. Looping with FLWOR. Because of the extremely verbose nature of XML it easy for these statements to become extraordinarly intimidating to look at. The way it get’s nested inside TSQL and the query function doesn’t help. But the concept actually isn’t that bad.  Like most code, writing your own is typically easier than trying to read someone else’s.

If you’re following along at home – I challenge you to write your own version of these queries. Give me a document with “Tampa Bay Rays” as the root element; add player position as an attribute to the player; sort it descending. Give it a try.  Post your questions if you have any.

For a much more extensive dive into XQuery you should go to books online. I’d also recommend looking into atomization and the data() function for some additional knowledge that can save you typing and frustration.

In my final post on this topic I’ll apply all this XML to the real world of the DBA, not just a made up xml doc dealing with fantasy baseball.

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

Information

This entry was posted on December 26, 2013 by in XPath XQuery XML.
%d bloggers like this: