I have an xml column in SQL Server 2005 that is the equivalent of:
我在SQL Server 2005中有一个xml列，相当于:
<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:
我希望能够获得Test的foo属性(根元素)的值作为varchar。我的目标是:
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
Msg 2390，级别16，状态1，第1行XQuery [value()]:不支持*属性节点
2 个解决方案
#1
41
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)
这适用于我(SQL Server 2005和2008)
Marc
马克
#2
4
If you dont know the root element:
如果你不知道根元素:
select @Data.value('(/*/@foo)[1]','varchar(20)') as Foo
#1
41
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)
这适用于我(SQL Server 2005和2008)
Marc
马克
#2
4
If you dont know the root element:
如果你不知道根元素:
select @Data.value('(/*/@foo)[1]','varchar(20)') as Foo