SQL Server: Leer Archivos XML usando TSQL, How to read XML Files using TSQL

SQL Server: Leer Archivos XML usando TSQL, How to read XML Files using TSQL 

Que tal  amigos, veremos cómo podemos leer archivos XML usando SQL Server, los archivos XML son muy útiles en muchos casos en particular, podemos usarlos para transferir datos entre aplicaciones, almacenar información adicional en columnas de tipo XML, entre otros casos más, desde la versión 2005 SQL Server ofrece un nuevo tipo de dato llamado XML, por lo cual si usan SQL Server 2005 o superior les recomiendo usar este tipo de dato y con él sus métodos.

 En el presente ejemplo veremos como leer un archivo XML sin tipo, este es un archivo no tiene un namespace que lo asocie a un esquema que valide los datos,

Como leer archivos XML sin namespace, Leer archivos XML sin tipo

 Tenemos el siguiente Archivo XML:

<ConfirmMessageKidaph NumeroEnvio="259870" >
  <NodoDetalle secuencia="0" 
 Codigo="KD-SEND-80103" Destino="Flour">
    <NodoAccion TipoAccion="F"/>
  </NodoDetalle>
  <NodoDetalle secuencia="1" 
 Codigo="KD-SEND-00048" Destino="Cable INC">
    <NodoAccion TipoAccion="F">
      <NodoFecha Estado="confirmado"
  year="2013" day="15" month="1" hour="8"
  min="30"/>
    </NodoAccion>
  </NodoDetalle>
</ConfirmMessageKidaph>

Para realizar la lectura en SQL Server realizar lo siguiente:

USE TEMPDB
GO

DECLARE @strXML xml;
SET @strXML ='
<ConfirmMessageKidaph NumeroEnvio="259870" >
  <NodoDetalle secuencia="0" 
 Codigo="KD-SEND-80103" Destino="Flour">
    <NodoAccion TipoAccion="F"/>
  </NodoDetalle>
  <NodoDetalle secuencia="1" 
 Codigo="KD-SEND-00048" Destino="Cable INC">
    <NodoAccion TipoAccion="F">
      <NodoFecha Estado="confirmado"
  year="2013" day="15" month="1" hour="8"
  min="30"/>
    </NodoAccion>
  </NodoDetalle>
</ConfirmMessageKidaph>'


SELECT
    A.n.value('@NumeroEnvio[1]', 'varchar(20)') AS NumeroEnvio,
    B.n.value('@secuencia[1]', 'varchar(10)') AS secuencia, 
    B.n.value('@Codigo[1]', 'varchar(20)') AS Codigo,
    B.n.value('@Destino[1]', 'varchar(30)') AS Destino,
    C.n.value('@TipoAccion[1]', 'varchar(20)') AS TipoAccion,
    D.n.value('@Estado[1]', 'varchar(20)') AS [Estado],
    D.n.value('@year[1]', 'varchar(20)') AS [year],
    D.n.value('@day[1]', 'varchar(20)') AS [day],
    D.n.value('@month[1]', 'varchar(20)') AS [month],
    D.n.value('@hour[1]', 'varchar(20)') AS [hour],
    D.n.value('@min[1]', 'varchar(20)') AS [min]
FROM
    @strXML.nodes('/ConfirmMessageKidaph') AS A(n)
    OUTER APPLY
    A.n.nodes('NodoDetalle') AS B(n)
    OUTER APPLY
    B.n.nodes('NodoAccion') AS C(n)
    OUTER APPLY
    C.n.nodes('NodoFecha') AS D(n);

GO

Si usas una versión inferior a SQL Server 2005, tenemos que usar OPENXML, hagan lo siguiente:

USE TEMPDB
GO

DECLARE @strXML varchar(max);
SET @strXML ='
<ConfirmMessageKidaph NumeroEnvio="259870" >
  <NodoDetalle secuencia="0" Codigo="KD-SEND-80103" Destino="Flour">
    <NodoAccion TipoAccion="F"/>
  </NodoDetalle>
  <NodoDetalle secuencia="1" Codigo="KD-SEND-00048" Destino="Cable INC">
    <NodoAccion TipoAccion="F">
      <NodoFecha Estado="confirmado" year="2013" day="15" month="1" hour="8" min="30"/>
    </NodoAccion>
  </NodoDetalle>
</ConfirmMessageKidaph>
'

DECLARE @IdDoc int;
EXEC sp_xml_preparedocument @IdDoc OUTPUT, @strXML
        SELECT * 
        FROM  OPENXML (@IdDoc, '/ConfirmMessageKidaph/NodoDetalle/NodoAccion', 1)
                WITH (
                      [NumeroEnvio]  varchar(20) '../../@NumeroEnvio',
                      [secuencia]  varchar(10) '../@secuencia',
                      [Codigo] varchar(20) '../@Codigo',
                      [Destino] varchar(30) '../@Destino',
                      [TipoAccion] varchar(20) '@TipoAccion',
                      [Estado] varchar(20) 'NodoFecha/@Estado',
                      [year] varchar(20) 'NodoFecha/@year',
                      [day] varchar(20) 'NodoFecha/@day',
                      [month] varchar(20) 'NodoFecha/@month',
                      [hour] varchar(20) 'NodoFecha/@hour',
                      [min]  varchar(20) 'NodoFecha/@min'
                      );

EXEC sp_xml_removedocument @IdDoc;
GO

Esta forma quita performance, además al final no deben de olvidar usar la sentencia sp_xml_removedocument ya que el documento analizado utiliza la octava parte de la memoria total asignada a SQL Server, así que liberen memoria.

EXEC sp_xml_removedocument @IdDoc;

Así que si tiene SQL Server 2005 o superior usen el tipo de datos XML.

Espero les haya servido, cualquier pregunta, no duden en hacerla.

Referencia: Herbert Mendoza (http://litigiouslobo.blogspot.com/)