Exportar datos en formato XML en SQL Server 2005

Me surgió este tema en el trabajo, y busque información por Internet. Hay numerosos ejemplos de importación pero los de exportación son muy escasos y confusos, así que decidí resumir de forma básica las “herramientas” que encontré en mi pequeña búsqueda. 

En este texto vamos a ver como generar un documento XML directamente haciendo una consulta sobre la base de datos y como guardar el resultado en un archivo XML físico en la maquina. Además veremos como alternativa como recuperar el resultado de una consulta de este tipo en .NET

 

Consultas de Selección para XML mediante FOR XML:

 

La consulta más básica de este tipo la podemos construir así:

 



 
SELECT * FROM TABLA FOR XML AUTO 
 

Y nos devolverá al parecido a esto:

 



 

<TABLA CAMPO1=»1″ CAMPO2=»correo1@pruebas.com»/>

<TABLA CAMPO1=»2″ CAMPO2=»correo2@ pruebas.com «/> 
 

Como veis de esta manera el XML devuelto no tiene nodo principal y cada subnodo lleva como nombre el nombre de la tabla y como atributos los nombres de cada campo y sus contenidos.

 

Existen otras dos formas básicas de hacer consultas FOR XML: RAW Y PATH

 



 SELECT * FROM TABLA FOR XML RAW
 

<row CAMPO1=»1″ CAMPO2=»correo1@pruebas.com»/>

<row CAMPO1=»2″ CAMPO2=»correo2@ pruebas.com «/> 
 

Con RAW los subnodos siempre de nombraran como «row»

 

Y PATH que a simple vista nos devolvería el mismo resultado que AUTO permite una mejor mezcla entre parámetros y atributos dentro de cada subnodo devuelto en el XML.

 

Os voy a mostrar un par de utilidades para mejorar el aspecto del XML devuelto aunque no me voy a centrar en mostrar todas. Podéis consultar el resto de opciones en:

 

http://msdn2.microsoft.com/es-es/library/ms190922.aspx

 

Podemos por ejemplo devolver documentos Atom de una manera sencilla así:

 



 SELECT TOP 2 ID_TABLA as ‘id’ FROM TABLA as item FOR XML AUTO, ROOT (‘feed’), ELEMENTS 

Que devolverá algo así:

 



 
<feed>         
   <item>
                    
       <id>1</id>
         
   </item>
         
   <item>
                    
      
<id>2</id>
         
   </item>
</feed> 
 

Primero señalar en los ALIAS de la tabla como de los campos son reconocidos por la consulta y añadidos al XML remplazando sus nombres originales.

 

El modificador ELEMENTS indica a la consulta que cada campo debe ir como un subnodo separado y no como un atributo.

 

Con el modificador ROOT añadimos un nodo principal al documento, y así de paso hacemos cumplir la normativa XML que indica que todo documento XML debe tener un único nodo principal.

 

Sin embargo en el caso de un documento Atom, para el nodo principal «feed» necesitamos declarar un espacio de nombres. Para lo cual SQL Server nos provee de la función WITH XMLNAMESPACES que deberemos poner justo antes de nuestra consulta de selección:

 



 WITH XMLNAMESPACES (DEFAULT ‘http://www.w3.org/2005/Atom’) 

SELECT TOP 2 ID_TABLA as ‘id’ FROM TABLA as item FOR XML AUTO, ROOT (‘feed’), ELEMENTS 
 

Y esto generará un documento más acorde con el estándar Atom:

 



 <feed xmlns=»http://www.w3.org/2005/Atom»>         
    <item>
                    
        <id>1</id>
         
    </item>
          
    <item>

        <id>2</id>


    </item>

</feed> 
 

Podéis ver la documentación para insertar más espacios de nombres a un nodo:

 

http://msdn2.microsoft.com/es-es/library/ms177607.aspx


Ahora que ya tenemos nuestro documento XML a nuestro gusto es la hora de guardarlo.

 

Para ello vamos a utilizar la función «xp_cmdshell» y el comando «sqlcmd».

 

La función «xp_cmdshell» requiere cambiar un poco la configuración de nuestra base de datos, para ello vamos a utilizar:

 




EXEC
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘xp_cmdshell’, 1;
GO
RECONFIGURE;

GO

 
 

Que nos dará permisos para utilizar dicha función.

 

Una vez reconfigurada nuestra base de datos vamos a crear un archivo de texto en el disco duro donde escribiremos nuestras consulta y que luego será leído por el comando «sqlcmd» y ejecutado.

 

Nuestra consulta será algo así:

 



 :XML ON 

USE nuestraBaseDeDatos
GO
SET NOCOUNT ON 

SELECT ‘<?xml version=»1.0″ encoding=»utf-8″ ?>’; 

WITH XMLNAMESPACES      
(
      ‘http://pruebas.com/2008/FS’ as fs,     
DEFAULT ‘http://www.w3.org/2005/Atom’
      )      

SELECT 
           
   ID_TABLA as ‘fs:recid’
     
FROM
           
   TABLA as item
           
FOR XML AUTO,
           
ROOT (‘feed’),
           
ELEMENTS
 

SET NOCOUNT OFF 
 

Vamos a explicar un poco esta consulta:

 

El modificador :XML ON indicará a la base de datos el tipo de datos que vamos a devolver. Si no ponemos este modificador es muy probable que lo que nos escriba posteriormente en el archivo sea ilegible. Este modificador solo es necesario usarlo si vamos a utilizar luego el comando «sqlcmd».

 

Modificamos el valor de NOCOUNT para evitar que en el archivo se grabe una línea muy molesta que indica el número de filas afectadas.

 

Además añadimos antes de la consulta la declaración del documento XML para que la inserte por encima del resultado de la consulta.

 

Esta consulta la grabamos en nuestro disco, por ejemplo en «c:consultaPrueba.sql».

 

Y ya podemos generar nuestro documento XML de esta forma:

 



 DECLARE @cmd VARCHAR(1000) 

SELECT @cmd = ‘sqlcmd -Uusuario -Pcontraseña -i «c:consultaPrueba.sql» -o «c:resultadoPrueba.xml»‘ 


EXEC
master..xp_cmdshell @cmd

 
 

Lo que nos generará un archivo «c:resultadoPrueba.xml» con el contenido de nuestra consulta.

 

http://msdn2.microsoft.com/es-es/library/ms175046.aspx

 

http://msdn2.microsoft.com/es-es/library/ms162773.aspx

 

Por ultimo me gustaría enseñaros como recuperar el contenido de una consulta de tipo FOR XML desde .NET:

 

Para ello tenemos que utilizar una función especial de nuestro SqlCommand, la función ExecuteXmlReader.

 

Debemos usarla ya que permite devoluciones de cadenas XML superiores a 2 mil caracteres que de otra manera se truncarían o seria devueltas en varios conjuntos de resultados.

 

Así pues quedaría algo así:

 




SqlConnection
con = new SqlConnection(cadenaConexion); 

SqlCommand comando = new SqlCommand(«SELECT ID_TABLA as ‘recid’ FROM TABLA as item FOR XML AUTO, ROOT (‘feed’), ELEMENTS», con); 

con.Open(); 

XmlReader reader = comando.ExecuteXmlReader(); 

XmlDocument doc = new XmlDocument(); doc.Load(reader); 

doc.PrependChild(doc.CreateNode(XmlNodeType.XmlDeclaration, «», «»));
 

con.Close();

 
 

En este caso es importante no cerrar la conexión antes de cargar el XmlReader en el XmlDocument, en el caso contrario obtendremos una excepción.

 

También he añadido como se insertaría en este caso la declaración del documento XML, como en el caso anterior.

 

Publicado también en:

 

http://www.moreplus.es


 

10 comentarios sobre “Exportar datos en formato XML en SQL Server 2005”

  1. Yo creo que mientras lo hagas bien.,no hay ningun problema es mas nos ayudas a difundir mas los conocimientos y a encontrar mas rapidos los temas…

    Suerte…

  2. estoy probando la misma cosa pero al generar el xml me genera con la primera linea en blanco.. como evito eso? para no tener que estar borrarndo cada tanto? porque si uso de esa forma el XSL no reconoce.. favor si podes responderme al mail jpelozo@gmail.com

  3. En el help del SQLCMD dice:
    «When multiple results are returned, sqlcmd prints a blank line between each result set in a batch. In addition, the « rows affected» message does not appear when it does not apply to the statement executed.»

    Para solucionarlo, hay que quitar el USE NuestraBasedeDatos y reemplazarlo por el parametro -d del sqlcmd, quedando:

    SELECT @cmd = ‘sqlcmd -Uusuario -Pcontraseña -dNuestraBasedeDatos -i «c:consultaPrueba.sql» -o «c:resultadoPrueba.xml»‘

  4. jorge
    para que quites la linea tienes que quitar el GO y USE DATABASE de la primera consulta.

    LUEGO EJECUTAS LO QUE INDICA ADRIAN

    SELECT @cmd = ‘sqlcmd -Uusuario -Pcontraseña -dNuestraBasedeDatos -i «c:consultaPrueba.sql» -o «c:resultadoPrueba.xml»‘

  5. Entendi a medias :S, podrias poner una demo en windows forms de .net para descargar con el ejemplo que planteas?, please!! 🙁
    ske no comprendo esto del XML, ni como se aplicaria a algun proyecto real, solo es util con aplicaciones web?, me parece que si, los archivos XML se almacenarian en el servidor?? :S
    entonces no sirve para Windows Forms? :S
    waa tengo tantas dudas 🙁

  6. Estoy ejecutando el código indicado y todo perfecto, pero cuando la base de datos está en una máquina (máquina A) y ejecuto mi programa en otra máquina (máquina B), genera el archivo xml en la máquina donde está la base de datos, es decir en la máquina A y lo que yo quiero es que el xml generado se quede en la máquina lócal donde tengo el ejecutable de mi software es decir en las máquinas B.

    Por favor pronta ayuda ya que saben que los usuarios no entienden de razones y quieren todo para ayer 😉
    Gracias

  7. Mi consulta es la siguiente, ejecute de manera exitosa todo lo que decis aca. el tema es qeu no me genera el XML con los datos si no que genera el resultado de la consulta nada mas.
    Esto es asi?
    como puedo hacer para generar el XML con los datos de la consulta?

    Saludos.

  8. he hecho todo cuanto se ha indicado.
    pero al final me genera el xml tipo consulta, no logro guardarlo en formato .xml
    me da el siguiente error.

    Sqlcmd: ‘c:consultaPrueba.sql’: nombre de archivo no válido.

    se supone que el crea el archivo verdad?

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *