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