Извлечение атрибутов из полей XML в таблице SQL Server 2008

У меня есть таблица с несколькими столбцами, один из которых является столбцом 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 в той же таблице?


sql-server-2008 xml-attribute
person James L.    schedule 06.04.2013    source источник

Ответы (1)


arrow_upward
42
arrow_downward

Сразу после того, как я разместил вопрос, я наткнулся на этот ответить. Не знаю, почему я не мог найти его в предыдущих поисках. Это был ответ, который я искал. Вот запрос, который работает:

Запрос

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
person James L.    schedule 06.04.2013
comment
+1. При использовании .value вы должны указать одно значение с выражением xPath. Вы знаете, что в XML есть не более одного значения, но SQL Server не знает об этом, поэтому вам нужно указать, что вы хотите найти первое значение, используя [1]. - person Mikael Eriksson; 06.04.2013