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 http://www.w3c.org. 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.
SELECT Picture, CategoryID FROM Categories WHERE CategoryID=1 FOR XML AUTO, ELEMENTS go XML_F52E2B61-18A1-11d1-B105-00805F49916B <Categories><Picture>dbobject/Categories[@CategoryID='1']/@Picture</Picture> <CategoryID>1</CategoryID></Categories>
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.
SELECT CategoryID, 'dbobject/Categories[@CategoryID='+ CONVERT(varchar(10), CategoryID)+']/@Picture' Picture FROM Categories WHERE CategoryID < 4 FOR XML RAW go XML_F52E2B61-18A1-11d1-B105-00805F49916B-------------------------------------------- <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"/>