Extract Attributes From XML in SQL Server 2005 Computed Functions

I was writing some computed functions to do property promotion of some data stored in an XML column in a SQL Server 2005 database. One of the properties was stored as a node attribute. So I wrote the following function.

CREATE FUNCTION dbo.get_foo_bar (@report XML)
RETURNS varchar(8) AS
BEGIN
RETURN CONVERT(VARCHAR(8), @report.query(''report/foo/@bar[1]''))
END

SQL Server Management Studio insisted I was making the following error.

XQuery [query()]: Attribute may not appear outside of an element

After a frustrating hour or two of tinkering and reading SQL Server documentation, some web searching finally turned up the hints I needed.

In short, the trick is to wrap the XPath expression in an XQuery data() function.

CREATE FUNCTION dbo.get_foo_bar (@report XML)
RETURNS varchar(8) AS
BEGIN
RETURN CONVERT(VARCHAR(8), @report.query(''data(report/foo/@bar[1])''))
END

The Hints