Importar datos a unas tablas desde XML

[Javichechu]

Hola amigos.
Veréis, he tenido que crear en mi empresa una serie de tablas vacías par aun cliente. Ese cliente tiene todas esas tablas llenitas de datos, y para no tener que picarlas, nos suministraron XML, uno por tabla, para rellenar con datos las tablas creadas.

Mi pregunta es acerca de cómo hago para leer los XML y volvar los datos de ellos en las tablas de mi base de datos SQL Server.

PD: Mi base de datos SQL Server 2005

PD2: Muchas gracias

[Javichechu]

Se que son fechas chungas... XD pero nadie puede ayudarme???

[Beakdan]

Ya que estás con SQL Server 2005 te conviene crear un paquete SSIS, para realizar la tarea de volcado. Es muy fácil configurar un XML Source para el documento que te puedan entregar.

Si no quieres o no tienes permitido usar paquetes SSIS, entonces puedes hacer el volcado con los métodos para el tipo de datos XML y XQuery. Te daré un pequeño ejemplo:

Suponiendo un archivo XML(E:\Carga\pedidos_detalle.xml) con la siguiente estructura:

Código:

<cliente id="35698">
  <pedido id="45126">
    <producto cod="2N2222" cant="100" imp="550" />
    <producto cod="CLC425" cant="20" imp="50" />
    <producto cod="MC68HC908" cant="3" imp="161.5" />
  </pedido>
  <pedido id="54126">
    <producto cod="MOC3030" cant="25" imp="200" />
  </pedido>
  <pedido id="67895">
    <producto cod="TIC32" cant="10" imp="125" />
    <producto cod="TIC36" cant="10" imp="125" />
  </pedido>
</cliente>
<cliente id="74813">
  <pedido id="78956">
    <producto cod="DS1820" cant="2" imp="250" />
    <producto cod="DS18B20" cant="2" imp="250" />
  </pedido>
  <pedido id="87452">
    <producto cod="LM75" cant="10" imp="39" />
  </pedido>
</cliente>

Primero, hay que volcarlo a una variable XML. Usando openrowset es tarea sencilla, pero debes activar "Ad Hoc Queries" para tu servidor de SQL Server (por defecto no están activadas). Una vez que se tiene el XML en una variable XML podemos obtener una estructura tabular de lo anterior con CROSS APPLY, XQuery y los métodos XML:

Código:

declare @doc xml
select    @doc = BulkColumn
from    openrowset(
            bulk 'E:\Carga\pedidos_detalle.xml', SINGLE_CLOB
        ) as xmlData

select    cliente.value('@id', 'int') AS IdCliente,
        pedido.value('@id', 'int') AS IdPedido,
        producto.value('@cod', 'varchar(15)') AS CodigoProducto,
        producto.value('@cant', 'int') AS Cantidad,
        producto.value('@imp', 'money') AS Importe
from    @doc.nodes('/cliente') as x1(cliente)
cross apply x1.cliente.nodes('./pedido') as x2(pedido)
cross apply x2.pedido.nodes('./producto') as x3(producto)

Lo que nos devuelve el siguiente resultado:

Código:

IdCliente   IdPedido    CodigoProducto  Cantidad    Importe
----------- ----------- --------------- ----------- ---------------------
35698       45126       2N2222          100         550.00
35698       45126       CLC425          20          50.00
35698       45126       MC68HC908       3           161.50
35698       54126       MOC3030         25          200.00
35698       67895       TIC32           10          125.00
35698       67895       TIC36           10          125.00
74813       78956       DS1820          2           250.00
74813       78956       DS18B20         2           250.00
74813       87452       LM75            10          39.00

Obviamente, si la estructura de tu XML no está anidada, el proceso es mucho más simple, pero este ejemplo puede guiarte.

En SQL Server 2000 se usaba OPENXML, pero nunca he probado si aún funciona en SS 2005.

Saludos

[Javichechu]

Gracias provaré con ello.
Pero si puedes dame algún detalle más sobre lo que es eso del SSIS porque no se lo que es y lo mismo me viene muy bien. Es la primera vez que hago volcados de éste tipo y estoy perdido.

Muchas gracias

[iislas]

Con el permiso de Beakdam, los SSIS (SQL SERVER INTEGRATION SERVICES), no es otra cosa que las DTS que se conocen en 2000

[Javichechu]

Iniciado por iislas

Con el permiso de Beakdam, los SSIS (SQL SERVER INTEGRATION SERVICES), no es otra cosa que las DTS que se conocen en 2000

Jeje. Y qué son las DTS???

Yo he trabajadon con SQLS pero más hacia la extracción de datos desde aplicaciones .Net, no tanto a nivel administrativo, u otras ramas que desconozco por completo, entre ellas lo que son las DTS

[Javichechu]

Iniciado por Beakdan

Obviamente, si la estructura de tu XML no está anidada, el proceso es mucho más simple, pero este ejemplo puede guiarte.

En SQL Server 2000 se usaba OPENXML, pero nunca he probado si aún funciona en SS 2005.

Saludos

Qué va, mi XML es mucho más sencillo. Tengo que hacer varios, pero tan sencillos cómo éste......

Código:

<?xml version="1.0" encoding="UTF-8" ?> 
- <PAISES>
  <PAIS CODIGO="1">España</PAIS> 
  <PAIS CODIGO="2">Estonia</PAIS> 
  <PAIS CODIGO="3">Finlandia</PAIS> 
  <PAIS CODIGO="4">Francia</PAIS> 
  <PAIS CODIGO="5">Grecia</PAIS> 
  <PAIS CODIGO="6">Holanda</PAIS> 
  <PAIS CODIGO="7">Hungría</PAIS> 
  <PAIS CODIGO="8">Islandia</PAIS> 
  <PAIS CODIGO="9">Irlanda</PAIS> 
  <PAIS CODIGO="10">Italia</PAIS> 
  <PAIS CODIGO="11">Letonia</PAIS> 
  <PAIS CODIGO="12">Liechtenstein</PAIS> 
  <PAIS CODIGO="13">Lituania</PAIS> 
  <PAIS CODIGO="14">Luxemburgo</PAIS> 
  <PAIS CODIGO="15">Malta</PAIS> 
  <PAIS CODIGO="16">Noruega</PAIS> 
  <PAIS CODIGO="17">Polonia</PAIS> 
  <PAIS CODIGO="18">Portugal</PAIS> 
  <PAIS CODIGO="19">Reino Unido</PAIS> 
  <PAIS CODIGO="20">República Eslovaca</PAIS> 
  </PAISES>

¿Cómo sería en éste caso? Y sobre todo cómo se activa "Ad Hoc Queries"? Lo digo porque lo de bulk con otro ejemplo que prové me daba error de sintaxis sin supueta razón por lo que sospecho que puede ser eso.

ah... y lo que intento es insertar los datos a una tabla que ya existe. En éste caso la tabla se llama "Paises" y tiene dos campos El Id del País, y el nombre del pais. Así pues hay que meter el insert por ahí de algún modo según tengo entendido.

[Beakdan]

Para esa estructura, el query es:

Código:

DECLARE @doc XML
SELECT  @doc = BulkColumn
FROM    OPENROWSET(
            BULK 'E:\Carga\paises.xml', SINGLE_CLOB
        ) AS Data
 
SELECT  paises.value('@CODIGO','INT') AS CodigoPais,
        paises.value('.', 'VARCHAR(20)') AS Pais
FROM    @doc.nodes('/PAISES/PAIS') AS x1(paises)
 

Para activar "Ad Hoc Queries", usa la herramienta "SQL Server Surface Area Configuraction". Seleccionas la opción "Surface Area Configuration for Features", en las opciones de "Database Engine" seleccionas "Ad Hoc Remote Queries". Marca "Enable OPENROWSET and OPENDATASOURCE suport".

Debes asignar permisos de "ADMINISTER BULK OPERATIONS" para el login que utilices en esta consulta.

DTS: Data Transformation Services. La herramienta ETL (extract, transform & load) que se incluía en versiones anteriores de SQL Server. Con SQL Server 2005 la herramienta ETL es SQL Server Integration Services. Al comparar ambas, diría que es como comparar un hacha de piedra con una navaja suiza. Hay mucha documentación sobre la herramienta, pero para aprender te recomiendo el libro "Professional SQL Server 2005 Integration Services" publicado por Wrox. Te aseguro que el gasto valdrá la pena.

[iislas]

Javichechu

DTS = DATA TRANSFORMATION SERVICES.

No es otra cosa, mas que los SERVICIOS de trasformacion de datos, ahora conocidos como ETL (Extracting, Trasforming and Loading).