XML Queries
Once the XML markup has been stored as an xml data type in the SQL Server 2005 relational table, you can use its tags, values, and attributes in SQL SELECT queries using the xml data type methods and XPATH syntax. The most useful query-related XML methods are as follows:
- exist(XPATH) method tests the presence of an XPATH expression in the XML document.
- value(XPATH,datatype) method returns the value of an XPATH expression as the specified SQL data type.
- query(XPATH) method returns the subset of an XML document specified by an XPATH expression.
- nodes(XPATH) method returns the selected XML nodes as SQL table variables.
For the rest of this article, let’s that assume we’ve created the following data in the Products table:
|
ProductID |
ProductName |
SupplierID |
Price |
Attributes |
|
1 |
BMW roadster model |
112 |
10.35 |
<root> <Color>Red</Color> <Size>10 in</Size> </root> |
|
2 |
T-shirt |
110 |
19.95 |
<root> <Color>Red</Color> <Color>Blue</Color> <Size>XL</Size> <Size>L</Size> <Size>M</Size> <Fabric>Cotton</Fabric> </root> |
|
3 |
Harry Potter and the Deathly Hallows |
934 |
34.99 |
-- null -- |
With these records in the Products table, you can use the SELECT statement in Listing 3 to select all products that have a color specified. (The //Color XPATH expression selects a Color tag anywhere in the XML document.)
Listing 3 Select products with a specified color.
SELECT ProductID,ProductName FROM Products WHERE Attributes.exist(’//Color’) = 1
The query returns these results:
|
ProductID |
ProductName |
|
1 |
BMW roadster model |
|
2 |
T-shirt |
The value function can extract values from the XML documents as SQL data types. However, it’s limited to extracting a single value, so you have to use the XPATH positional syntax on top of the query expression to ensure that the XML query returns a singleton value. For example, to get the product number, its name, and color, you could use the following SELECT statement in Listing 4. (Notice the parentheses around the XPATH expression followed by the [1] selector.)
Listing 4 Retrieve product color from XML document.
SELECT ProductID,ProductName, Attributes.value(’(//Color)[1]’,’varchar(MAX)’) AS Color FROM Products
As we’ve used the value function in the query, the results contain a single row per product, with the first product color in the Color column:
|
ProductID |
ProductName |
Color |
|
1 |
BMW roadster model |
Red |
|
2 |
T-shirt |
Red |
|
3 |
Harry Potter and the Deathly Hallows |
NULL |
If you’d like to retrieve all product colors, you have to use the nodes method to generate a temporary table of colors, and perform a Cartesian product (cross-join) of the temporary table with the original products table. If you find this description confusing, you’re right—and the SELECT statement itself is also pretty complex, as shown in Listing 5.
Listing 5 Retrieve all colors for all products.
SELECT ProductID,ProductName, A.node.value(’(.)[1]’,’varchar(MAX)’) AS Color FROM Products CROSS APPLY Attributes.nodes(’//Color’) AS A(node)
However, the results are exactly what we wanted:
|
ProductID |
ProductName |
Color |
|
1 |
BMW roadster model |
Red |
|
2 |
T-shirt |
Red |
|
2 |
T-shirt |
Blue |
The path to the results involved a number of steps:
- The Attributes.nodes function created a temporary table with a single column. The AS keyword assigned temporary names to the table (A) and its column (node).
- The SELECT statement performed a cross-join between the Products table and the A table, resulting in rows that contained every possible combination of product data and colors (encoded as XML fragments).
- The A.node.value function extracted the root element value from the XML fragment. (And we know that the nodes function extracted Color tags as root elements of the temporary table.)
To further illustrate what the nodes method does, consider the results returned by the query in Listing 6.
Listing 6 Get color-related XML fragments from product #2.
SELECT A.node.query(’.’) AS Result FROM Products CROSS APPLY Attributes.nodes(’//Color’) As A(node) WHERE Products.ProductID = 2
|
Result |
<Color>Red</Color> |
<Color>Blue</Color> |