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

Solution 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

Solution 2

If you dont know the root element:

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

Comments

[Ads]

what if you don't know what the root element is..??? in this case we know it is "Test", but I have an XML field that has different XML depending on the value in another field. so it could be <book> or <something_else>... is there a default root element name we can use..???

[StuartLC]

@Ads use a wildcard, e.g. //Element[@foo] or if you know something about the structure /*/Elements/Element[@foo]

[dudeNumber4]

Wrap the query in parens. Sheesh; I would've never guessed. The XML querying in TSQL works great, but the syntax is way too difficult.