This is the third instalment in a series of articles that I’m hoping will serve as a primer for Oracle developers interested in learning about Xquery. And – confession time – I’ve left the best part for last. However, you really should start with Part 1. Good things come to those who wait.
In the first article we talked about XPath which is – and is not – a part of XQuery, and how, despite its powerful simplicity, it begins to buckle under the weight of longer, more complex tasks. And its weaknesses really begin to show when a task calls for querying more than one XML document at the same time; you know, the way you might join two tables in a SQL query.
And that is why, in 2007, the seeds of XQuery FLWOR sprouted.
FLWOR
FLWOR is pronounced flower – and, just so we can return to serious business, let me get all the puns out of my system in one go: FLWOR rose in 2007, and it quickly blossomed into the most beautiful thing in the XQuery garden.
While XQuery shares custody of XPath with XSLT, FLWOR solely belongs to it. Indeed, when people say XQuery, they’re often just referring to FLWOR. As we’ll soon see, it is a powerful functional language, and while its syntax may be slightly different, the DNA that runs through it will be immediately familiar to any Oracle developer.
Let’s start by breaking down what FLWOR stands for.
Example XML Document
But before we can do that we need an XML document to work with. Let’s use the one we created in the first two parts of this series.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
<?xml version="1.0" encoding="UTF-8"?> <friends> <characters> <character status="Lead" actorid="001"> <firstname>Ross</firstname> <lastname>Geller</lastname> <gender>Male</gender> <birthdate>1967-10-18</birthdate> <occupation>Palaeontologist</occupation> </character> <character status="Lead" actorid="002"> <firstname>Phoebe</firstname> <lastname>Buffay</lastname> <gender>Female</gender> <birthdate>1965-02-16</birthdate> <occupation>Masseuse</occupation> </character> <character status="Secondary" actorid="007"> <firstname>Janice</firstname> <lastname>Goralnik</lastname> <gender>Female</gender> </character> </characters> <cast> <actor id="001"> <firstname>David</firstname> <lastname>Schwimmer</lastname> <birthdate>1966-11-02</birthdate> </actor> <actor id="002"> <firstname>Lisa</firstname> <lastname>Kudrow</lastname> <birthdate>1962-07-30</birthdate> </actor> <actor id="007"> <firstname>Maggie</firstname> <lastname>Wheeler</lastname> <birthdate>1961-08-07</birthdate> </actor> </cast> </friends> |
View friends.xml in a separate window
A First FLWOR Expression
Based on our XML, let me throw together a simple FLWOR expression. It’ll make explaining the acronym’s component parts so much easier.
|
1 2 3 4 5 |
for $x in //friends/characters/character let $stat := "Lead" where $x/@status = $stat order by $x/lastname ascending return $x/lastname|$x/firstname |
(Please bear in mind that XQuery is a case sensitive language and FLWOR really should be flwor since the keywords must be lower case. Additionally, even though we’ve used every clause in our example above, they are all – with the exception of return – optional; the only other rule is that you must have at least one of for or let.)
F
The F word in FLWOR is For. And if this seems familiar to you, it’s because it works in pretty much the same way that you’re used to in SQL; it establishes the criteria by which we’ll be iterating through the data.
Stripped to the bare bones, its similarity to a PL/SQL for loop is obvious:
|
1 2 |
for $i in (1 to 100) return $i |
|
1 2 3 |
FOR i IN 1 .. 100 LOOP dbms_output.put_line(i); END LOOP; |
In our earlier FLWOR example, we’re iterating through our XML document and selecting everything within the character nodes into our $x variable.
(By the way, variables in XQuery must be prefixed with a $. And, although they may contain numbers, they must begin with an alpha character. They also cannot contain special characters. Most developers tend to keep them simple and stick to things like $x, but whenever I’m feeling disciplined I try to make my variable names a little descriptive.)
The final thing I need to say about the for clause at this juncture is that you can have more than one of them in your FLWOR expression. We’ll talk about that later.
L
L is for Let. The Let keyword allows you explicitly declare a variable and feed it a value. And, as with the for clause, you can have more than one let clause in your FLWOR expression.
Here’s an overly simplistic example:
|
1 2 3 4 |
let $itemPrice := 5.99 let $quantitySold := 15 let $totalSales := $itemPrice * $quantitySold return $totalSales |
In our earlier example we’re declaring a variable named $stat for the status attribute and populating it with the string “Lead”.
W
W is for Where.
I don’t need to say much more, do I? If there’s anything we know all about as Oracle developers it’s where clauses. Where clauses in FLWOR are pretty similar to SQL where clauses; they filter the dataset/nodeset. The XPath expression in the for clause in our example says that we want all the character nodes in our XML document; the where clause limits it to only those with a status attribute of “Lead”.
But since the for clause is XPath, you might ask, couldn’t we have simply filtered it using an XPath predicate?
|
1 2 3 |
for $x in //friends/characters/character[@status="Lead"] order by $x/lastname ascending return $x/lastname|$x/firstname |
The answer is yes. They’re exactly the same thing. There are arguments to be made for both methods, but ultimately it comes down to what you’re comfortable with – and as an Oracle developer the where clause gets my vote every time.
O
O is for order by, and in FLWOR order by clauses are, again, similar to what you’re used to in SQL. As in SQL it’s an optional clause; and, again like SQL, if it exists it must be positioned after the where clause. As with where clauses, order by clauses must apply to the nodeset(s) selected in the for clause(s).
R
R is for Return. The SQL analog for the FLWOR return is, I guess, the select list. It is what defines the result of the FLWOR expression.
Return clauses, in FLWOR, are mandatory and must be positioned last.
In our example, the return clause is a simple one; it returns the lastname and firstname nodes. However, return clauses can be as complicated as they need to be, and it is not uncommon in FLWOR to include some last-minute data manipulation in the return clause. Returning to our predicate versus where clause debate, we could even rewrite the expression as:
|
1 2 3 4 |
for $x in //friends/characters/character let $stat := "Lead" order by $x/lastname ascending return $x[@status = $stat]/lastname|$x[@status = $stat]/firstname |
XQuery and Oracle
I probably don’t need to, but I just want to pause here to remind you that if you want to run any of the FLWOR expressions we’re discussing in your Oracle database, you’ll need to employ the functions we talked about in the previous article. So instead of:
|
1 2 3 4 5 |
for $x in //friends/characters/character let $stat := "Lead" where $x/@status = $stat order by $x/lastname ascending return $x/lastname|$x/firstname |
You’ll run something along the lines of:
|
1 2 3 4 5 6 7 8 |
SELECT XMLQUERY ('for $x in //friends/characters/character let $stat := "Lead" where $x/@status = $stat order by $x/lastname ascending return $x/lastname|$x/firstname' PASSING data RETURNING CONTENT) FROM sitcoms WHERE id = 1; |
However, for clarity’s sake I’ve been leaving the Oracle-ese out of my examples and sticking to pure FLWOR. That’s alright with you, right? Good, let’s continue.
Multiple Fors
I promised to show you how you can achieve something that looks very much like an SQL join using FLWOR. This is the point where XPath would go running home to its mummy, crying like a baby; but, for FLWOR, joins are a pretty straightforward prospect. Let’s say, for example, that using our Friends XML we wanted to output a list of characters and the actors that played them. We’d write something like this:
|
1 2 3 4 5 6 7 |
for $character in //friends/characters/character for $cast in //friends/cast/actor let $char_actid := $character/@actorid let $cast_actid := $cast/@id where $char_actid = $cast_actid and $character/@status = "Lead" return $character/lastname|$character/firstname|$cast/lastname|$cast/firstname |
What we’re doing becomes obvious on closer inspection. We get all the characters in $character and we get our cast in $cast, and then we join them using the id attribute.
FLWOR “joins” can get more complex than this, pretty much as intricate as anything we could fashion in SQL.
Conclusion
Speaking of SQL reminds me that we should be speaking of PL/SQL. Because FLWOR has functionality that is analogous to some of the things we see in PL/SQL – from conditional statements to loops. We’ll look into that in the next, and final, article in the series.
Load comments