从SQL Server 2008表中提取XML字段的属性

Question

从SQL Server 2008表中提取XML字段的属性

sql-server-2008xml-attribute

30

我有一张带有多个列的表，其中一个是xml列。我没有命名空间可用于查询。所有记录的XML数据结构始终相同。

虚构的数据

create table #temp (id int, name varchar(32), xml_data xml)

insert into #temp values
(1, 'one',   '<data><info x="42" y="99">Red</info></data>'),
(2, 'two',   '<data><info x="27" y="72">Blue</info></data>'),
(3, 'three', '<data><info x="16" y="51">Green</info></data>'),
(4, 'four',  '<data><info x="12" y="37">Yellow</info></data>')

期望的结果

Name    Info.x   Info.y   Info
-----   -------  -------  -------
one       42       99     Red
two       27       72     Blue
three     16       51     Green
four      12       37     Yellow

部分工作

select Name, xml_data.query('/data/info/.').value('.', 'varchar(10)') as [Info]
from   #temp

它返回Name和Info列。我无法弄清如何在不使用命名空间的情况下提取属性值。例如，以下查询会返回错误：

查询1

select Name, xml_data.query('/data/info/@x') as [Info]
from   #temp

Msg 2396, Level 16, State 1, Line 12
XQuery [#temp.xml_data.query()]: Attribute may not appear outside of an element

查询2

select Name, xml_data.value('/data/info/@x', 'int') as [Info]
from   #temp

Msg 2389, Level 16, State 1, Line 12
XQuery [#temp.xml_data.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

查询 3

select Name, xml_data.query('/data/info/.').value('@x', 'int') as [Info]
from   #temp

Msg 2390, Level 16, State 1, Line 9
XQuery [value()]: Top-level attribute nodes are not supported

问题

如何编写查询以返回同一表中的常规列数据和xml列中的元素+属性值？

- James L.

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- James L. · Accepted Answer

刚刚我发布了这个问题，然后偶然发现了这个答案。不知道为什么之前的搜索中找不到它。这就是我正在寻找的答案。以下是可以使用的查询：

查询

select Name
      ,xml_data.value('(/data/info/@x)[1]', 'int') as [Info.x]
      ,xml_data.value('(/data/info/@y)[1]', 'int') as [Info.y]
      ,xml_data.value('(/data/info/.)[1]', 'varchar(10)') as [Info]
from   #temp

结果

Name     Info.x    Info.y    Info
-------  --------  --------  ---------
one         42        99     Red
two         27        72     Blue
three       16        51     Green
four        12        37     Yellow

------ 编辑 [2014-01-29] ------

我发现另一个值得添加到这个答案的情况。假设在 <data> 元素中有多个 <info> 元素，可以使用 cross apply 返回所有 <info> 节点。

create table #temp (id int, name varchar(32), xml_data xml)

insert into #temp values
(1, 'one',   '<data><info x="42" y="99">Red</info><info x="43" y="100">Pink</info></data>'),
(2, 'two',   '<data><info x="27" y="72">Blue</info><info x="28" y="73">Light Blue</info></data>'),
(3, 'three', '<data><info x="16" y="51">Green</info><info x="17" y="52">Orange</info></data>'),
(4, 'four',  '<data><info x="12" y="37">Yellow</info><info x="13" y="38">Purple</info></data>')

select Name
      ,C.value('@x', 'int') as [Info.x]
      ,C.value('@y', 'int') as [Info.y]
      ,C.value('.', 'varchar(10)') as [Info]
from #temp cross apply
     #temp.xml_data.nodes('data/info') as X(C)

drop table #temp

这个例子返回以下数据集：

Name      Info.x      Info.y      Info
--------- ----------- ----------- ----------
one       42          99          Red
one       43          100         Pink
two       27          72          Blue
two       28          73          Light Blue
three     16          51          Green
three     17          52          Orange
four      12          37          Yellow
four      13          38          Purple