Browse DevX
Sign up for e-mail newsletters from DevX


A Taste of XQuery for the DBA : Page 2

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

'C' is for Cookie: Selects in T-SQL and XQuery
The rest of this article will introduce you to XQuery by way of the already familiar Transact-SQL language.

Let's say that the bakers ordered too much salt and want to get rid of some. This simple T-SQL query finds all cookie recipes that have salt as one of the ingredients:

select Cookie.* from dbo.Cookie join dbo.Ingredient on Ingredient.Ingredient2Cookie = Cookie.id where Ingredient.name = 'Salt'

To get the same information from the corresponding XML documents, you need to use one of the new methods on fields of type XML. These new methods take XQuery expressions as string arguments, like this:

select * from dbo.Cookie where CookieInXML.exist('/cookie/ingredients/ingredient[name="Salt"]') =1

In this case, we're using the exist method which (duh.) checks for the existence of some node or value. Take a closer look at the text of the actual XQuery:


This is XQuery at its simplest, using only a path expression. XPath is a slightly older XML technology used to identify nodes within an XML document. This expression checks for the existence of "Salt" within the ingredients of each cookie.

Table 1. XPath Basics: This table introduces two XPath building blocks.
Query Element Discussion
/cookie/ingredients/ingredient A slash is used to separate nested data. In this case the English language translation is something like, "Show me all 'ingredient' elements nested directly within 'ingredients' elements nested directly within the 'cookie' element.

[name = "Salt"] Square brackets, known as a "predicate expression," are used to filter data. Here, it selects all ingredients with a name of "Salt"—roughly analogous to SQL's WHERE clause.

Now I'll show you another translation from T-SQL to XQuery. Below is some T-SQL joining two tables in order to list all the ingredients in a particular recipe.

SELECT Ingredient.Name, Ingredient.Quantity, Ingredient.Unit FROM dbo.Ingredient JOIN dbo.Cookie ON Ingredient.Ingredient2Cookie = Cookie.ID where Cookie.Name = 'Oatmeal Chocolate Chip'

Here's the output:

Name Quantity Unit ------------------------- ------------- --------- Vanilla Extract 1 Teaspoon Butter 1 Cup White Sugar 0.5 Cup Milk 1 Cup Salt 1 Teaspoon Eggs 2 Each Chocolate Chips 1 Cup Brown Sugar 1 Cup All Purpose Flour 2 Cup Rolled Oats 3 Cup Baking Soda 1 Teaspoon (11 row(s) affected)

The XQuery equivalent is also simple:

Select CookieInXML.query('/cookie//ingredient') from dbo.Cookie where CookieInXML.exist('/cookie[@name = "Oatmeal Chocolate Chip"]') =1

Here's some of the output, which, not surprisingly, is in XML:

<ingredient> <name>Vanilla Extract</name> <quantity unit="Teaspoon">1</quantity> </ingredient> <ingredient> <name>Butter</name> <quantity unit="Cup">1</quantity> </ingredient> ...

This query isn't much more complicated than the first, except this one uses two XPath expressions: The exist method filters rows, and the query method selects useful data from the XML document.

Table 2. More Basics: Three more building blocks include “@”, “//”, and “query”.

Query Element Discussion
query The query method displays data retrieved by the XQuery in the SQL result set.
@name The "at" symbol is used within XPath to specify an attribute.
/cookie//ingredient The double slash is used to select all ingredient elements within the cookie element regardless of depth.

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