Home > Articles > Data > SQL Server

  • Print
  • + Share This

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>
  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.