How do I select a top level attribute of an xml column in SQL Server 2005?

Question

I have an xml column in SQL Server 2005 that is the equivalent of:

<Test foo="bar">
  <Otherstuff baz="belch" />
</Test>

I want to be able to get the value of the foo attribute of Test (the root element) as a varchar. My goal would be something along the lines of:

select cast( '<Test foo="bar"><Otherstuff baz="belch" /></Test>' as xml).value('@foo','varchar(20)') as Foo

When I run the above query I get the following error:

Msg 2390, Level 16, State 1, Line 1 XQuery [value()]: Top-level attribute nodes are not supported

Answer 1

Just a guess: try /Test@foo.

Answer 2

John Saunders has it almost right :-)

declare @Data XML
set @Data = '<Test foo="bar"><Otherstuff baz="belch" /></Test>'

select @Data.value('(/Test/@foo)[1]','varchar(20)') as Foo

This works for me (SQL Server 2005 and 2008)

Marc

Answer 3

      /*/@foo

Unless a noncompliant XML Parser / XPath engine is used, there is no need to specify a position in the node-set of selected nodes as for example in "[1]", as there is always only one top element in an XML document and an element can have at most one attribute with a given name.