SQL Server XQuery, Accessing Attributes

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:

• Retrieval Functions; and
• Predicate Functions.