Извлечение атрибутов из полей 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

Вопрос

Как вы пишете запрос для возврата регулярных данных столбца и значений атрибутов element + из столбца xml в той же таблице?

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