While browsing this blog's usage logs recently, I've noticed quite a few referrals from various search engines of users trying to troubleshoot problems they may have encountered when accessing XML attributes in a SQL Server XQuery.
Although the problem has been discussed in the past, and has even been documented in Books Online, some users out there still aren't familiar with a SQL Server 2005 limitation regarding the querying of XML attributes – and the error message itself isn't of much help either. That's why I've decided to point this problem out in my blog as well to help improve the visibility of this issue.
For example, when querying this XML:
declare @books xml set @books = N'<xml version="1.0"> <Books xsi:noNamespaceSchemaLocation="PubsTitles.xsd"> <Book TitleId="BU1032"PubDate="1991-06-12T00:00:00.000Z"> <Title>The Busy Executive''s Database Guide</Title> <Type>business</Type> <PubId>1389</PubId> <Price>19.9900</Price> <Royalty>10</Royalty> <YtdSales>4095</YtdSales> <Notes>An overview of available database systems with emphasis on common business applications. Illustrated.</Notes> </Book> <Book TitleId="BU1111"PubDate="1991-06-09T00:00:00.000Z"> <Title>Cooking with Computers: Surreptitious Balance Sheets</Title> <Type>business</Type> <PubId>1389</PubId> <Price>11.9500</Price> <Royalty>10</Royalty> <YtdSales>3876</YtdSales> <Notes>Helpful hints on how to use your electronic resources to the best advantage.</Notes> </Book> </Books>'
...one would instinctively formulate the query like this:
select Books.Book.query('Title').value('.', 'varchar(max)') as Title
,Books.Book.query('@TitleId').value('.', 'varchar(6)') as TitleId
,Books.Book.query('@PubDate').value('.', 'datetime') as TitleId
from @Books.nodes('/Books/Book') Books (Book)
However, referencing XML attributes this way is not allowed in SQL Server 2005, and this is the error message we would have encountered if we were to follow our instincts:
Msg 2396, Level 16, State 1, Line 26 XQuery [query()]: Attribute may not appear outside of an element
Not quite as eloquent as one would expect, wouldn't you agree?
fn:data() to the rescue!
By using the data() XQuery function like so:
select Books.Book.query('Title').value('.', 'varchar(80)') as Title
,Books.Book.query('data(@TitleId)').value('.', 'varchar(6)') as TitleId
,Books.Book.query('data(@PubDate)').value('.', 'datetime') as TitleId
from @Books.nodes('/Books/Book') Books (Book)
...the query succeeds as expected:
Title TitleId TitleId ---------------------------------------------------- ------- ----------------------- The Busy Executive's Database Guide BU1032 1991-06-12 00:00:00.000 Cooking with Computers: Surreptitious Balance Sheets BU1111 1991-06-09 00:00:00.000
This is true both for typed as well as untyped XML instances, and the function is, of course, documented in Books Online.
ML
p.s. I've decided on posting this example while preparing a more detailed overview of XPath/XQuery functions in SQL Server, which should follow soon.
p.p.s. The overview is divided into two parts:
