All Purpose FLWOR
XPath can only take you so far. Much of the SQL-style eloquence of XQuery comes by way of FLWOR expressions (pronounced "flower" or "flour" either of which lends itself to all kinds of bad jokes). The term is usually written in all caps because it's a mnemonic to help you remember each clause in an expression: for
, order by
, and return
Now imagine that the CookieCo bakers are running out of large measuring cups. In an effort to conserve, they want a list of all ingredients used in quantities greater than a single cup. Here's the query written in T-SQL:
AND Unit = 'Cup'
Here's an equivalent XQuery using a FLWOR expression:
for $ingredient in /cookie//ingredient/quantity[@unit="Cup"]/..
XQuery works with sets of data. The query above takes the results of the XPath expression, filters them through the where clause, and outputs the results with no decoration using the return clause.
Table 3. Introducing FLWOR: Going beyond path expressions, FLWOR adds power and flexibility to XQuery.
||Associates an iterator with the results of an expression. In a FLWOR expression, at least one for or let clause is required.
||XQuery prefaces variables with a dollar sign ("$"). In the example, the variable $ingredient is used for iterating through the results of the expression in the for clause.
||Selects the parent element of the current node; used in the example to select the entire <ingredient> element rather than just the <quantity> element.
||Uses a Boolean expression to filter the results; similar to SQL's WHERE clause .
||Constructs the output of the FLWOR expressionroughly analogous to SQL's select list.
The FLWOR mnemonic also includes two optional clauses not used above:
- let: Associates a single variable (rather than an iterator) with the results of an expression (not yet available in SQL Server 2005 as of Beta 3).
- order by: Orders results very much like the ORDER BY clause in T-SQL.
Of course, this is just the smallest taste of the power of FLWOR expressions. But for you DBAs, the expressions should be fairly intuitive and well worth the time it takes to master them.
Next I want to talk about data manipulation. Imagine that CookieCo's insurance company calls (maybe after a couple of mishaps in the bakery). The agent insists on adding a safety note in the directions portion of every cookie recipe.
Inserting the new step in the relational database is easy enough, although it requires two statements because the instructions are numbered. First, you need to make room in the sequence. Then you can insert the new step into the first position.
SET Sequence = Sequence + 1
1,'Take a moment to ensure the safety of your work area.',ID
In XML, position is meaningful, so the equivalent XQuery has only one step:
<step>Take a moment to ensure the safety of your work area.
Table 4. A first look at XML DML: XML data manipulation language is similar to its relational equivalent.
||XPath equivalent of SQL's INSERT statement.
||Predicate used for selecting the Nth node. Since tables contain rows with no inherent order, SQL has no built-in equivalent.
All of which is pretty straightforward. But take another look at the last XPath expression:
Why is the whole thing encased in parentheses? The reason is that the target of the insert clause must be a single node. Without the parenthesis, the bracketed numeral would specify the first <directions>
element of every <cookie>
element, which could result in more than one insertion.
Now imagine that the bakers go on strike. They feel insulted by the safety information, and refuse to pick up their aprons until it's removed. After days of tense negotiations, the CookieCo management decides to delete the safety notice rather than undermine its relationship with the union.
Below is the T-SQL to remove the first step from every recipe. Again, this SQL script requires two statements: one to do the deletion and one to reorder the sequence.
WHERE Sequence = 1
SET Sequence = Sequence - 1
And again, XQuery equivalent only needs a single statement:
Simple enough, right? An XPath expression specifies the first step of each set of directions; the delete keyword deletes the results of the expression.
Finally, we'll do an update. This time, a new heath craze, The Vanilla Diet, sends vanilla costs through the roof. To get a handle on the crisis, management decrees that all recipes should cut back immediately.
Here's the T-SQL:
SET Quantity = .05
WHERE Name = 'Vanilla Extract'
And here's the XQuery:
('replace value of
(/cookie/ingredients/ingredient[name = "Vanilla Extract"]/quantity/text())
Table 5. More XML DML: XQuery's update syntax works a lot like SQL's.
|replace value of
||Analogous to the SET keyword in SQL's UPDATE statement.
||Used because the RecipeInXml field is untyped XML. The function ensures that the target will be treated as text rather than complex content.
If the job of a language is to make easy things easy, XQuery is a great success. In SQL Server 2005, fields of type XML have methods. There are five methods total; we've worked with three: exist, modify, and query. Each method takes an XQuery in a string as an argument. The simplest XQuery is just an XPath expression (which can be quite powerful despite the simplicity) and for more complicated queries, you can use FLWOR expressions, which work similar to SQL queries.
XQuery is every bit as ambitious as SQL, so it's impossible to do it justice in fewer than 2,000 words. But the fact that many of the XQuery design team are database professionals should give the rest of us SQL-nerds a head start.