XML A TABLA

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

1.