Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


A Taste of XQuery for the DBA : Page 3

Do you need to query semistructured data? Or maybe you just need another competency (a.k.a. buzzword) for your resume? XQuery can be both things and more, and it's coming with SQL Server 2005.




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Date: 1/31/2018 @ 2 p.m. ET

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, let, where, 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:

SELECT Name, Unit, Quantity FROM dbo.Ingredient WHERE Ingredient.Quantity>1 AND Unit = 'Cup'

Here's an equivalent XQuery using a FLWOR expression:

SELECT CookieInXml.query(' for $ingredient in /cookie//ingredient/quantity[@unit="Cup"]/.. where $ingredient/quantity>1 return $ingredient ') FROM dbo.Cookie

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.

Query Element Discussion
for Associates an iterator with the results of an expression. In a FLWOR expression, at least one for or let clause is required.
$ingredient 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.
where Uses a Boolean expression to filter the results; similar to SQL's WHERE clause .
return Constructs the output of the FLWOR expression—roughly 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.

Data Manipulation
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.

UPDATE dbo.Direction SET Sequence = Sequence + 1 INSERT dbo.Direction (Sequence,Text,Direction2Cookie) SELECT 1,'Take a moment to ensure the safety of your work area.',ID FROM dbo.Cookie

In XML, position is meaningful, so the equivalent XQuery has only one step:

UPDATE dbo.Cookie SET CookieInXML.modify ('insert <step>Take a moment to ensure the safety of your work area. as first into (/cookie/directions)[1] ')

Table 4. A first look at XML DML: XML data manipulation language is similar to its relational equivalent.

Query Element Discussion
insert XPath equivalent of SQL's INSERT statement.
[1] 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.

DELETE dbo.Direction WHERE Sequence = 1 UPDATE dbo.Direction SET Sequence = Sequence - 1

And again, XQuery equivalent only needs a single statement:

UPDATE dbo.Cookie SET CookieInXML.modify ('delete /cookie/directions/step[1]')

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:

UPDATE dbo.Ingredient SET Quantity = .05 WHERE Name = 'Vanilla Extract'

And here's the XQuery:

UPDATE dbo.Cookie SET CookieInXml.modify ('replace value of (/cookie/ingredients/ingredient[name = "Vanilla Extract"]/quantity/text())[1] with .05 ' )

Table 5. More XML DML: XQuery's update syntax works a lot like SQL's.

Query Element Discussion
replace value of Analogous to the SET keyword in SQL's UPDATE statement.
text() Used because the RecipeInXml field is untyped XML. The function ensures that the target will be treated as text rather than complex content.

What's Next?
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.

Eric McMullen is a director at Falstaff Solutions, a Denver-based consulting house which specializes in data-centric .NET applications. Check out Falstaff's Web site at www.falstaffsolutions.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date