The Basics of XML Path Language (XPath)

XPath queries are used to locate and retrieve XML elements, attributes, and their values. XPath's name is derived from its syntactical resemblance to file paths. You can find out more about the XPath standard by consulting the XML standards body on the World Wide Web Consortium at Because XPath is a standard in its own right, the discussion in this chapter will be limited to the XPath syntax used to address database objects in SQL Server 2000.

The syntax for retrieving the value of a binary column is as follows:


In XPath, the @ symbol (think attribute) refers to an XML attribute. The / character indicates element depth?just like it indicates directory depth in file paths. The XPath query Top/belowTop[@pk='1']/@col, for example, indicates that you are requesting the value of an XML attribute called col belonging to an element belowTop that is a child element of Top and has an attribute pk whose value is 1. The square brackets thus indicate a subexpression that is applied to the attribute or element to its left. As usual, attribute names correspond to columns, and element names correspond to tables.

dbobject represents the virtual name of type dbobject. dbobject-type virtual names allow direct access to database objects from the Internet.

TableName represents the name of a table (think XML element) that you are accessing. PrimaryKeyName represents the primary key column name (think XML attribute) of the table specified in TableName. PrimaryKeyValue is the value of PrimaryKeyName for the row that contains the selected value. The primary key is specified to guarantee the uniqueness of the object returned when you execute this XPath query.

As an example:


dataobjects represents the virtual name called dataobjects of type dbobject that you created when you created the NorthwindVdir virtual directory.

Categories represents the name of the table (think XML element) that you are accessing. CategoryID represents the primary key column name (think XML attribute) of the table specified in TableName. '1' is the value of CategoryID for the row that contains this value.

If you change the code in Listing 41.6, adding the primary key to the select list and removing BINARY BASE from the FOR XML clause, you get the results in Listing 41.8.

Listing 41.8 FOR XML AUTO Generates XPath References to Binary Data
SELECT Picture, CategoryID FROM Categories
WHERE CategoryID=1




You can force SQL Server to return XPath references to binary data in RAW and EXPLICIT mode in the following way. Select the primary key of the table, and then build a string representation based on the XPath syntax discussed earlier by selecting the primary key again and concatenating it to the @CategoryID expression. Listing 41.9 shows how to retrieve data from the Picture column in Categories using this method.

Listing 41.9 Creating the XPath Manually Using FOR XML RAW

SELECT CategoryID,
       CONVERT(varchar(10), CategoryID)+']/@Picture' Picture
FROM Categories
WHERE CategoryID < 4


<row CategoryID="1" Picture="dbobject/Categories[@CategoryID=1]/@Picture"/>
<row CategoryID="2" Picture="dbobject/Categories[@CategoryID=2]/@Picture"/>
<row CategoryID="3" Picture="dbobject/Categories[@CategoryID=3]/@Picture"/>

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features