sql-server-2008 - leer - tipo de dato xml en sql server

Extracción de atributos de los campos XML en la tabla de SQL Server 2008

Question

Tengo una tabla con varias columnas, una de las cuales es una columna xml . No tengo un espacio de nombres para usar en la consulta. Los datos XML son siempre la misma estructura para todos los registros.

Datos comprobados

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>'')

Resultados deseados

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

Funciona parcialmente

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

Devuelve las columnas de Name e Info . No puedo averiguar cómo extraer los valores de los atributos sin usar un espacio de nombres. Por ejemplo, las siguientes consultas devuelven errores:

Consulta 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

Consulta 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 *''

Consulta 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

Pregunta

¿Cómo se escribe una consulta para devolver datos de columnas regulares y valores de atributo + elemento de una columna xml en la misma tabla?

Answer 1

Justo después de que publiqué la pregunta, me topé con esta answer . No sé por qué no pude encontrarlo en búsquedas anteriores. Era la respuesta que estaba buscando. Aquí está la consulta que funciona:

Consulta

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

Resultado

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

.

------ Editar [2014-01-29] ------

Encontré otro caso que vale la pena agregar a esta respuesta. Dados los múltiples elementos <info> dentro del elemento <data> , es posible devolver todos los nodos <info> mediante 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

Este ejemplo devuelve el siguiente conjunto de datos:

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