XML to TABLE
A continuación te mostraré como leer una cadena XMLdesde SQL Server para convertirlo en un formato tipo tabla.
Iniciamos con un XML sencillo:
<cliente>
<id>57</id>
<id>58</id>
<id>59</id>
</cliente>
Para leer el xml que muestro en la parte superior se hace lo siguiente:
SELECT
t.c.value('text()[1]','int') as IdCliente
from @xmlCliente.nodes('//id') as t(c)
@xmlCliente es la variable que contiene la cadena del XML.
Observa que 'text()[1]' es utilizado para leer el valor de los elementos de este XML
En este ejemplo se está accediendo directamente a los nodos <id> => '//id'
Ejemplo completo:
El xml en cadena se asigna a una variable de tipo XML:
DECLARE @xmlCliente as xml
set @xmlcliente =
'<cliente>
<id>57</id>
<id>58</id>
<id>59</id>
</cliente>';
SELECT t.c.value('text()[1]','int') as IdCliente
from @xmlCliente.nodes('//id') as t(c)
= > 
EJEMPLO 2
Ahora bien, si dentro de tu XML tienes atributos y deseas leerlos,
se hace de la siguiente manera:
1.- Cuando el Atributo esta en el elemento padre, entonces debemos subir al nodo un nivel,
ya que el ejemplo anterior se colocó directamente en el nodo <Id>.
<cliente name="Juanito Perez">
<id>57</id>
<id>58</id>
<id>59</id>
</cliente>
Se selecciona el nodo del elemento principal <cliente>
con un alias nombre tabla y nombre columna
y de ese mismo nodo principal, ahora en lugar de seleccionar el contenido
del elemento (con 'text()') ahora seleccionamos el valor del atributo 'name'
colocando una @ para atributos.
SELECT
t.c.value('@name[1]','varchar(20)') as Nombre
,t2.c2.value('text()[1]','int') as Id
from @xmlCliente.nodes('//cliente') as t(c)
cross apply t.c.nodes('id') as t2(c2)
Para acceder al valor de los elementos, se debe obtener del nodo principal
t.c.nodes, el cual realizamos un cross apply...
EJEMPLO COMPLETO:
DECLARE @xmlCliente as xml
SET @xmlcliente =
'
<cliente name="Juanito Perez">
<id>57</id>
<id>58</id>
<id>59</id>
</cliente>
';
SELECT
t.c.value('@name[1]','varchar(20)') as Nombre
,t2.c2.value('text()[1]','int') as Id
from @xmlCliente.nodes('//cliente') as t(c)
cross apply t.c.nodes('id') as t2(c2)
2.- Cuando los elementos secundarios contiene atributos:
<cliente name="Juanito Perez">
<id value="A"> 57 </id>
<id value="B"> 58 </id>
<id value="C"> 59 </id>
</cliente>
Solo resta agregar la columna como atributo '@' ya que esos
elementos se tienen en t2(c2)
,t2.c2.value('@value[1]','varchar(1)') as value
EJEMPLO COMPLETO:
DECLARE @xmlCliente as xml
set @xmlcliente =
'
<cliente name="Juanito Perez">
<id value ="A" > 57 </id>
<id value ="B" > 58 </id>
<id value ="C" > 59 </id>
</cliente>
';
SELECT
t.c.value('@name[1]','varchar(20)') as Nombre
,t2.c2.value('text()[1]','int') as Id
,t2.c2.value('@value[1]','varchar(1)') as value
from @xmlCliente.nodes('//cliente') as t(c)
cross apply t.c.nodes('id') as t2(c2)
=>
Entonces concluimos que el contenido
de un elemento se obtiene con 'text()[1]'
y el valor de un atributo con el prefijo '@'
XML con namespaces
DECLARE
@xmlCliente as xml
SET
@xmlcliente =
'
<ns:cliente name="Juanito
Perez"
xmlns:ns="http://google.com" >
<ns:id>57</ns:id>
<ns:id>58</ns:id>
<ns:id>59</ns:id>
</ns:cliente>
';
WITH
XMLNAMESPACES (
'http://google.com' as
"ns" )
SELECT
t.c.value('@name[1]','varchar(20)') as Nombre
,t2.c2.value('text()[1]','int') as Id
from @xmlCliente.nodes('//ns:cliente') as t(c)
cross
apply t.c.nodes('ns:id') as t2(c2)
<Basket xmlns="http://tempuri.org/basketDayArchives.xsd">
<BasketList data="2003-01-02" val="30.05"> 1 </BasketList>
<BasketList data="2003-01-03" val="30.83"> 2 </BasketList>
<BasketList data="2003-01-06" val="30.71"> 3 </BasketList>
</Basket>
;WITH XMLNAMESPACES
(
'http://tempuri.org/basketDayArchives.xsd' AS ns
)
SELECT
y.value (N'@val [1]', N'varchar(100)') AS Valor
,y.value (N'@data [1]', N'varchar(100)') AS Fecha
,y.value (N'text() [1]', N'varchar(100)') AS valorElemento
FROM @XML.nodes(N'//ns:Basket') t(c)
OUTER APPLY c.nodes ('ns:BasketList') as r(y)
select @XML
