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

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