sql-server-2008之从 SQL Server 2008 表中的 XML 字段中提取属性_编程设计

我有一个包含几列的表格，其中一个是 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列。我无法弄清楚如何在不使用 namespace 的情况下提取属性值。例如，以下查询返回错误:

查询 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 返回常规列数据和元素 + 属性值同一个表中的列？

请您参考如下方法:

就在我发布问题后，我偶然发现了这个 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] ------

我发现了另一个值得添加到这个答案中的案例。给定多个 <info> <data> 内的元素元素，可以返回所有 <info>节点使用 cross apply :

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