从 SQL Server 2008 表中的 XML 字段中提取属性 [英] Extracting Attributes from XML Fields in SQL Server 2008 Table
问题描述
我有一个包含多个列的表，其中一个是 xml 列.我没有要在查询中使用的命名空间.XML 数据对于所有记录始终具有相同的结构.
I have a table with several columns, one of which is a xml column. I do not have a namespace to use in the query. The XML data is always the same structure for all records.
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 列.我无法弄清楚如何在不使用名称空间的情况下提取属性值.例如，以下查询返回错误:
It returns the Name and Info columns. I cannot figure out how to extract the attribute values without using a namespace. For instance, the following queries returns errors:
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 列返回常规列数据和元素 + 属性值?
Question
How do you write a query to return regular column data, and element + attribute values from an xml column in the same table?
推荐答案
就在我发布问题后，我偶然发现了这个 答案.不知道为什么我在之前的搜索中找不到它.这是我一直在寻找的答案.这是有效的查询:
Just after I posted the question, I stumbled across this answer. Don't know why I couldn't find it in prior searches. It was the answer I was looking for. Here is the query that works:
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
.
我发现了另一个值得添加到此答案中的案例.给定 元素内的多个
I found another case that is worth adding to this answer. Given multiple <info> elements within the <data> element, it is possible to return all <info> nodes by using 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
此示例返回以下数据集:
This example returns the following dataset:
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
这篇关于从 SQL Server 2008 表中的 XML 字段中提取属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
