'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
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:
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.
. XPath Basics: This table introduces two XPath building blocks.
||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.
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:
CookieInXML.exist('/cookie[@name = "Oatmeal Chocolate Chip"]') =1
Here's some of the output, which, not surprisingly, is in XML:
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”.
||The query method displays data retrieved by the XQuery in the SQL result set.
||The "at" symbol is used within XPath to specify an attribute.
||The double slash is used to select all ingredient elements within the cookie element regardless of depth.