mayo 2008 – La catarsis de la programación

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:

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

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í:

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:

</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;

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

Mes: mayo 2008

El mes de los { heroes } en Second Nug

Exportar datos en formato XML en SQL Server 2005