26

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

Michael Liu
  • 52,147
  • 13
  • 117
  • 150
Alex Argo
  • 8,920
  • 12
  • 43
  • 46

3 Answers3

46

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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 or ... is there a default root element name we can use..??? – Ads Feb 20 '13 at 10:12
  • 2
    @Ads use a wildcard, e.g. `//Element[@foo]` or if you know something about the structure `/*/Elements/Element[@foo]` – StuartLC Apr 05 '14 at 13:22
  • 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. – dudeNumber4 Jan 20 '18 at 22:01
7

If you dont know the root element:

select @Data.value('(/*/@foo)[1]','varchar(20)') as Foo
bojidar alexandrov
  • 123
  • 2
  • 4
0

Why does .value('@foo', 'varchar(20)') generate the error “Top-level attribute nodes are not supported”?

When you query the xml data type, the context is the document node, which is an implicit node that contains the root element(s) of your XML document. The document node has no name and no attributes.

How can I get the value of an attribute on the root element?

In your XQuery expression, include the path to the first root element:

DECLARE @Data xml = '<Customer ID="123"><Order ID="ABC" /></Customer>'

SELECT @Data.value('Customer[1]/@ID', 'varchar(20)')
-- Result: 123

If you don’t know (or don’t want to specify) the name of the root element, then just use * to match any element:

SELECT @Data.value('*[1]/@ID', 'varchar(20)')
-- Result: 123

Because the query context is the document node, you don’t need to prefix the XQuery expression with a forward slash (as the other answers unnecessarily do).

Why do I have to include [1]?

The XQuery expression you pass to value() must be guaranteed to return a singleton. The expression Customer/@ID doesn’t satisfy this requirement because it matches both ID="123" and ID="456" in the following example:

DECLARE @Data xml = '<Customer ID="123" /><Customer ID="456" />'

Remember that the xml data type represents an XML document fragment, not an XML document, so it can contain multiple root elements.

What’s the difference between Customer[1]/@ID and (Customer/@ID)[1]?

The expression Customer[1]/@ID retrieves the ID attribute of the first <Customer> element.

The expression (Customer/@ID)[1] retrieves the ID attribute of all <Customer> elements, and from this list of attributes, picks the first.

The following example demonstrates the difference:

DECLARE @Data xml = '<Customer /><Customer ID="123" /><Customer ID="456" />'

SELECT @Data.value('Customer[1]/@ID', 'varchar(20)')
-- Result: NULL (because the first Customer element doesn't have an ID attribute)

SELECT @Data.value('(Customer/@ID)[1]', 'varchar(20)')
-- Result: 123
Michael Liu
  • 52,147
  • 13
  • 117
  • 150