LEER XML EN SQL (SELECT XML)

Buenos Días. Volviendo de vacaciones y compartiendo lo siguiente.

Muchas veces nos encontramos con situaciones que las soluciones suelen ser algo extravagantes, en esta entrada compartiré un tip que luego estará en el esquema del curso, ¿de qué se trata?, leer los XML como una tabla.

XML en SQL contiene muy buenas ventajas para quienes lo usen adecuadamente, es un tipo de variable especifico, es nativo, es de fácil declaración del lado de la programación, es flexible para trabajar, etc…

Entonces la pregunta es, a fines prácticos ¿en que nos puede ayudar?, enumero algunas situaciones.

• Envió de múltiples variables desde un sistema a la base de datos
• Envió de tablas desde un sistema a la base de datos en un esquema altamente entendible
• Recepción de servicios SOAP y operarlo directamente sobre el SQL sin pasar por ningún tipo de software externo, parsing, etc… ¿dije SOAP?, SI… En algún momento les mostrare como consumir servicios desde SQL y que configuración adicional tenemos que incorporar, pero eso lo dejaremos para más adelante :)

Estos son algunos de los usos posibles del XML sobre SQL, y sin duda tendremos infinidades de casos en que usarlo será el camino más óptimo a nuestro problema, y sin duda, nos puede ayudar a brindar una solución mucho más profesional.

Obviare la explicación de los que es una cadena en formato XML, por lo tanto, lo explicare muy muy simple… Es un METALENGUAJE, que permite etiquetar y organizar los elementos, muchos archivos y sistemas utilizan XML para trabajar, inclusive una extensión tan popular como el SVG se apoya sobre XML para definirle las características.

Empecemos…

Utilizaremos para este ejercicio un ejemplo escrito para poder trabajar con el, donde definiremos una variable XML, la cargaremos y luego realizamos la lectura del mismo, tenemos muchas formas de leer XML en SQL, les mostrare unas de tantas, para mi gusto la mas simple, pero eso es solo una apreciación mía.

Lo primero que haremos es crear la variable XML y cargarla con contenido.

DECLARE @VARIABLE_XML XML

SET @VARIABLE_XML = '<tabla1>
<fila><campo1>216000</campo1><campo2>3</campo2></fila>
<fila><campo1>217000</campo1><campo2>4</campo2></fila>
<fila><campo1>218000</campo1><campo2>5</campo2></fila>
<fila><campo1>219000</campo1><campo2>6</campo2></fila>
</tabla1>'

Como se puede apreciar, existe un tipo de variable en SQL que es XML, y al mismo luego le incorporamos un valor XML, en este caso muy sencillo, se puede apreciar que es un XML donde pasamos una TABLA1, el mismo esta compuestos por “FILA”, y luego está dividido en “CAMPO”, si nos fijamos cada “CAMPO” tiene valores, vamos a aprender a leerlos!!!

SELECT 
TABLA1.ID.value('campo1[1]','INT') AS CAMPO_1,
 TABLA1.ID.value('campo2[1]','NVARCHAR(4000)') AS CAMPO_2
  FROM @VARIABLE_XML.nodes('/tabla1/fila') as TABLA1(ID) 

Bien, en la anterior consulta se puede apreciar la forma de leerlo, esta es un tipo de consulta utilizando Xquery en SQL, el mismo funciona por defecto, por tanto no necesitamos de realizar ningún tipo de configuración adicional.

Explicando la query anterior, esto funciona de la siguiente forma:

Como se puede ver es una consulta SELECT clásica, donde seleccionamos los valores de un FROM, en este caso el FROM es un XML, donde instanciamos la variable XML, y luego buscamos los NODOS, nosotros por el tipo de estructura del XML llegamos hasta el nodo FILA, y luego una vez instanciado hasta el nodo fila, generamos un ALIAS y dentro del mismo le generamos una columna (lo que esta entre paréntesis), no detallare mucho sobre porque se define de esta forma, pero SQL devuelve una especie de TABLA en memoria.

Vamos ahora al SELECT, aquí como se puede ver, la estructura es, TABLA.COLUMNA.VALOR([NODO XML], [TIPO DE VALOR]) as NOMBRE_COLUMNA.

Esto es, llamamos a la tabla y al valor de la columna, y entre paréntesis instanciamos el valor que queremos utilizar dentro de la estructura XML, en estos casos CAMPO1 y CAMPO2, y luego muy importante tenemos que definir qué tipo de dato es, si es un INT, VARCHAR, DATETIME, etc…

Muy bien!, sé que puede parecer un poco confuso al principio, pero es cuestión de practicarlo un poco y veremos que es muy práctico. A continuación pondré un ejemplo un poco más complejo en el que podemos observar que se pueden realizar cruces sin ningún tipo de problema, tanto dentro del XML como con tablas externas, en este caso estoy enviando dos tablas dentro del mismo XML y cruzándolas en la consulta.

 
DECLARE @VARIABLE_XML XML

SET @VARIABLE_XML = '<tabla1>
<fila><campo1>216000</campo1><campo2>3</campo2></fila>
<fila><campo1>217000</campo1><campo2>4</campo2></fila>
<fila><campo1>218000</campo1><campo2>5</campo2></fila>
<fila><campo1>219000</campo1><campo2>6</campo2></fila>
</tabla1>
<tabla2>
<fila><campo1>316000</campo1><campo2>3</campo2></fila>
<fila><campo1>317000</campo1><campo2>4</campo2></fila>
<fila><campo1>318000</campo1><campo2>5</campo2></fila>
<fila><campo1>319000</campo1><campo2>6</campo2></fila>
</tabla2>'


SELECT  TABLA1.ID.value('campo1[1]','INT') AS TABLA1_CAMPO_1,
   TABLA1.ID.value('campo2[1]','NVARCHAR(4000)') AS TABLA1_CAMPO_2,
   TABLA2.ID.value('campo1[1]','NVARCHAR(4000)') AS TABLA2_CAMPO_1
    FROM  @VARIABLE_XML.nodes('/tabla1/fila') as TABLA1(ID)
INNER JOIN  @VARIABLE_XML.nodes('/tabla2/fila') as TABLA2(ID) 
 ON
  TABLA1.ID.value('campo2[1]','NVARCHAR(4000)') = TABLA2.ID.value('campo2[1]','NVARCHAR(4000)')

Excelente!, invito a probar la siguiente consulta para que vean el comportamiento  del mismo!!!

Bueno eso es todo, cualquier consulta no duden en preguntar.

Espero que les sea de mucha utilidad, hasta la próxima.

SQL EXPRESS y el problema del BACKUP AUTOMATICO

Hoy vamos a incorporar una entrada que seguramente le será útil a más de uno que esté utilizando SQL EXPRESS como servidor de base de datos, el gran problema que nos encontramos con este motor de base de datos, es la ausencia del agente, y por lo tanto no podemos automatizar nuestros procesos de respaldo con el planificador.

Sin embargo, con un pequeño script, y un archivo de ejecución por lote podemos conseguir, en el caso del respaldo, el mismo resultado que utilizando el agente, nada más que en vez de utilizar el planificador de SQL, utilizaremos el planificador de Windows.

Lo primero que tendremos que hacer, es generar un archivo con extensión SQL, este archivo contendrá nuestro script y lo armaremos de tal forma que modificarlo para diferentes utilidades sea muy sencillo.

--BACKUP DE TODAS LAS BASES

PRINT 'REALIZO BACKUP DE TODAS LAS BASES' 

DECLARE @NOMBRE                 VARCHAR(50) 
DECLARE @UBICACION              VARCHAR(256)   
DECLARE @NOMBRE_ARCHIVO         VARCHAR(256)  
DECLARE @FECHA_ARCHIVO          VARCHAR(20) 
  
-- Especifico la ubicacion del directorio de respaldo

SET @UBICACION = 'C:\bases\'   

-- Me grabo en variable la fecha que contendra todas las bases que estaremos respaldando

SELECT @FECHA_ARCHIVO = CONVERT(VARCHAR(20),GETDATE(),112)   

DECLARE VAL_CURSOR CURSOR FOR  

SELECT NAME FROM master.dbo.sysdatabases  
WHERE NAME NOT IN ('master','model','msdb','tempdb')   -- Incorporo las bases de datos que NO QUIERO RESPALDAR 

--RECORRO EL CURSOR Y GENERO LOS RESPALDOS UTILIZANDO EL SIGUIENTE BUCLE

OPEN VAL_CURSOR   

FETCH NEXT FROM VAL_CURSOR INTO @NOMBRE    
  
WHILE @@FETCH_STATUS = 0   
BEGIN   
    --GRABO EN VARIABLE EL NOMBRE DEL ARCHIVO FINAL
    SET @NOMBRE_ARCHIVO = @UBICACION + @NOMBRE + '_' + @FECHA_ARCHIVO + '.BAK'  

    --INSTANCIO LA SENTENCIA PARA GENERAR EL BACKUP EN DISCO
    BACKUP DATABASE @NOMBRE TO DISK = @NOMBRE_ARCHIVO   
    FETCH NEXT FROM VAL_CURSOR INTO @NOMBRE   
END     

--LIBERO EL CURSOR DEL SERVIDOR

CLOSE VAL_CURSOR   
DEALLOCATE VAL_CURSOR
 
GO

Podemos ejecutar dicho script por consola, el mismo tiene que guardar en el destino configurado todos los respaldos no excluidos.

Una vez realizado este script y probado lo guardamos en la carpeta correspondiente, como por ejemplo ‘\BASES\SCRIPTS\BACKUP.SQL’

Ahora utilizaremos el planificador de Windows para poder ejecutar dicho script, para ello emplearemos el muy poco usado SQLCMD, este es un ejecutable que abre un intérprete de SQL bastante simple a nivel de línea de comandos.

REM sqlcmd -S NOMBRE\INSTANCIA -E  -i C:\bases\scripts\backup.sql

REM Instancio el motor de base de datos con SQLCMD, y utilizo el script generado anteriormente ni bien se genera la conexion

sqlcmd -S LOCALHOST\SQLEXPRESS -E  -i C:\bases\scripts\backup.sql

REM En caso de que el servidor no este en dominio y no pueda utilizar un usuario de red, puedo generar una unidad de red y guardar el respaldo en una carpeta de red

xcopy "c:\bases\*.bak" "z:\backup"

REM Puedo borrar los archivos de respaldos una vez realizada la copia a una unidad de red.

del "c:\bases\*.bak"

Creamos un BAT con el contenido anterior y lo instanciamos desde una consola de comandos, el mismo debería de conectarse al servidor de base de datos y generar los respaldos utilizando el QUERY anteriormente generado, si esto mismo lo incorporamos en el planificador de Windows, el resultado será tener cada día los respaldos en dicho carpeta, diferenciados por la fecha de creación.

Espero que les sea de mucha utilidad, hasta la próxima.

Adjunto ambos script para Descargar