sqlsql-serverxmlxpathxquery-sql

How do I select a top-level attribute of an XML column in SQL Server?

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

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

I want 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

But 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

  • 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