Firmas invitadas
 

Implementación de argumentos variables mediante XML en SQL Server 2000

 
Publicado el 02/Nov/2006
Actualizado el 02/Nov/2006
Autor: Rubén Vigón
 

Cómo pasar un número variable de argumentos a un procedimiento almacenado de SQL Server usando XML, evitando el uso de SQL dinámico (sp_executesql).


Cómo pasar un número variable de argumentos a un procedimiento almacenado de SQL Server usando XML, evitando el uso de SQL dinámico (sp_executesql)

 

SQL Server 2000 incluye un nuevo conjunto de funciones para el manejo de datos en formato XML. Por ejemplo, es posible devolver datos al cliente en formato XML mediante la habitual sentencia SELECT junto a la cláusula FOR XML, o transformar los datos contenidos en un documento XML en un conjunto de filas (registros) con los que trabajar de manera clásica (con sentencias Transact-SQL como SELECT, INSERT, UPDATE o DELETE) mediante OpenXML.

 

Una de las posibilidades que nos ofrecen éstas nuevas funciones es la implementación de argumentos variables en los procedimientos almacenados. OpenXML permite trabajar con los datos contenidos en un documento XML (en realidad, una cadena de texto plano) como si de un conjunto de registros se tratase, de manera que podemos agregar un argumento a un procedimiento almacenado que reciba una cadena XML donde se incluyan todos los parámetros que este procedimiento necesite. De ésta manera, evitamos el uso de SQL dinámico (sp_executesql) para ejecutar instrucciones Transact-SQL generadas en tiempo de ejecución y los inconvenientes que su uso acarrea (como la peligrosa inyección de código SQL) además de obtener un mejor rendimiento (las instrucciones ejecutadas con EXECsp_executesql no son compiladas hasta el momento de la ejecución y el plan de ejecución generado por el optimizador de consultas raramente es reutilizado).

 

¿Cómo funciona OpenXML?

 

OpenXML transforma una cadena de texto con estructura XML en un conjunto de registros estándar (como, por ejemplo, el conjunto de registros devuelto por una sentencia SELECT). Para obtener correctamente éstos datos, debemos especificar algunos parámetros de OpenXML; los más comunes son:

 

OPENXML(num_doc int, patron_filas nvarchar) [WITH (esquema_filas)]

 

·         num_doc: Controlador interno del documento (un número entero obtenido mediante sp_xml_preparedocument)

·         patron_filas: Patrón XPath que identifica cuáles de los nodos del documento XML se procesarán

·         esquema_filas: Definición del tipo de datos que contienen los nodos del documento XML, en formato [nombre_columna, tipo_dato_columna]

 

Veamos un pequeño ejemplo donde se obtiene un conjunto de registros de dos columnas a partir de un documento XML con la siguiente estructura:

 

<paises>

      <pais IdPais="1" Nombre="Francia"/>

      <pais IdPais="2" Nombre="Italia"/>

</paises>

 

 

Fig.1 – Ejemplo de obtención de registros mediante OpenXML

 

Con ésta característica de SQL Server podemos resolver un problema muy común en gran número de aplicaciones de gestión: permitir al usuario seleccionar los registros que desea que aparezcan en un determinado informe. Si nuestra tabla tiene un campo que identifique inequívocamente un registro, sólo tenemos que “armar” la cadena XML que incluya todos éstos identificadores y pasársela al procedimiento almacenado, para su uso en las correspondientes sentencias SELECT internas.

 

¿Por qué no usar SQL dinámico?

La solución habitual a éste problema es el uso de SQL dinámico: generar una cadena de texto con las sentencias necesarias para obtener los datos que necesitamos y ejecutarlas mediante EXEC() o sp_executesql; por ejemplo:

 

DECLARE @ids varchar(20)

SET @ids = '5, 6, 7, 8'

EXEC('SELECT * FROM Empleados WHERE IdEmpleado IN (' + @ids + ')')

 

Sin duda, ésta es una característica muy potente de SQL Server, pero conviene usarla con cuidado y tener en cuenta sus principales inconvenientes, entre los que destacaría los siguientes:

 

·         Las sentencias SQL se ejecutan en un alcance distinto del procedimiento que las llamó, por lo que no pueden tener acceso a sus parámetros y variables locales.

·         Las tablas temporales creadas en las sentencias SQL se eliminan al terminar éstas (por lo que no es posible trabajar con ellas desde el procedimiento que las ejecuta); las opciones SET establecidas en estas sentencias tampoco afectan al procedimiento externo

 

Para más información sobre este tema, recomiendo encarecidamente la lectura del siguiente artículo de Erland Sommarskog (Microsoft® MVP SQL Server) traducido por Simon Hayes:

 

Las virtudes y maldades del SQL dinámico

http://www.hayes.ch/sql/sql_dinamico.html

 

Caso práctico:

 

Veamos como el soporte para XML de SQL Server puede ayudarnos en un caso real. Supongamos que tenemos una tabla Empleados con varios cientos de registros, y una aplicación de gestión donde el usuario puede hacer altas, bajas y modificaciones de éstos datos, así como generar informes basados en ellos. Tenemos un formulario donde el usuario puede seleccionar los empleados que desea que aparezcan en un determinado informe:

 

 

Fig.2 – Formulario de ejemplo para la selección de los empleados a incluir en el informe

 

Vamos a desarrollar un procedimiento almacenado que reciba un argumento XML (en realidad, una cadena de texto varchar) que contendrá la información con la cual el procedimiento podrá identificar y devolver los registros que el usuario desea imprimir.

 

¡¡Manos a la obra!!:

 

Vamos a crear una base de datos en nuestro servidor y una tabla “Empleados” que utilizaremos para nuestro proyecto de prueba:

 

/* Crear la base de datos PruebasXML */

CREATE DATABASE PruebasXML

GO

USE PruebasXML

GO

/* Crear la tabla Empleados */

CREATE TABLE Empleados

(

IdEmpleado int IDENTITY(1, 1) PRIMARY KEY,

Nombre varchar(20),

Apellido1 varchar(20),

Apellido2 varchar(20),

FechaNacimiento datetime

)

GO

/* Insertar algunos registros */

INSERT INTO Empleados VALUES ('Jorge', 'Martínez', 'García', '19760703')

INSERT INTO Empleados VALUES ('Pedro', 'López', 'Valle', '19680319')

INSERT INTO Empleados VALUES ('Luis', 'Iglesias', 'González', '19961107')

INSERT INTO Empleados VALUES ('Alfredo', 'Muñiz', 'Suárez', '19790901')

INSERT INTO Empleados VALUES ('Sergio', 'Rodríguez', 'Alvarez', '19810430')

INSERT INTO Empleados VALUES ('Rafael', 'Fuentes', 'Aranda', '19661022')

INSERT INTO Empleados VALUES ('Miguel', 'Arias', 'Gómez', '19700101')

GO

 

Una vez creadas la base de datos y la tabla e insertados algunos registros, la vista desde el “Analizador de consultas” de SQL Server sería la siguiente:

 

 

Fig.3 – Vista previa de la tabla Empleados en el Analizador de consultas de SQL Server

 

Crearemos ahora el procedimiento almacenado que devolverá un determinado conjunto de empleados, según la cadena XML recibida:

 

CREATE PROCEDURE spInformeEmpleados @TextoXML varchar(2000) AS

SET NOCOUNT ON

DECLARE @DocXML int

/* Cargar el documento XML del texto del argumento @TextoXML */

EXEC sp_xml_preparedocument @DocXML output, @TextoXML

/* Seleccionar los registros de la tabla Empleados cuyo campo IdEmpleado esté incluido

en el nodo <empleados/empleado/IdEmpleado> del documento XML */

SELECT * FROM Empleados WHERE IdEmpleado IN (

      SELECT IdEmpleado FROM OpenXML(@DocXML, 'empleados/empleado') WITH (IdEmpleado int)

) ORDER BY Apellido1, Apellido2

/* Destruir el documento XML y liberar memoria */

EXEC sp_xml_removedocument @DocXML

SET NOCOUNT OFF

 

Éste procedimiento almacenado recibe, en su argumento @TextoXML, una cadena XML que define (a través de su identificador IdEmpleado) los registros que deseamos que sean devueltos por el procedimiento. Esta cadena XML tendrá un formato similar al siguiente:

 

<empleados>

      <empleado IdEmpleado="1"/>

      <empleado IdEmpleado="3"/>

      <empleado IdEmpleado="4"/>

      <empleado IdEmpleado="6"/>

</empleados>

 

El siguiente paso será crear una pequeña aplicación en Visual Basic 6.0 y ADO que haga uso de éste procedimiento almacenado como origen de datos para un informe. En el evento Load del formulario (Fig.1) cargamos los empleados, leyéndolos de la tabla Empleados y mostrándolos en un control ListView:

 

Private Sub Form_Load()

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset

Set Cn = New ADODB.Connection

Set Rs = New ADODB.Recordset

Cn.Open "Provider=SQLOLEDB.1;Initial Catalog=PruebasXML;Data Source=SERVIDORSQL"

Rs.Open "SELECT * FROM Empleados ORDER BY Apellido1, Apellido2", Cn, adOpenForwardOnly

While Not Rs.EOF

      '// Cargar el control ListView con los empleados de la base de datos

      With lvwEmpleados.ListItems.Add(, , Rs.Collect("Nombre"), , 1)

            .Tag = Rs.Collect("IdEmpleado")

            .SubItems(1) = Rs.Collect("Apellido1")

            .SubItems(2) = Rs.Collect("Apellido2")

            .SubItems(3) = Rs.Collect("FechaNacimiento")

      End With

      Rs.MoveNext

Wend

Rs.Close: Set Rs = Nothing

Cn.Close: Set Cn = Nothing

End Sub

 

En el evento Click del botón “Aceptar” (btnAceptar) llamamos al procedimiento almacenado (mediante un objeto ADODB.Command) enviando como único argumento la cadena XML que contiene los identificadores de los empleados seleccionados:

 

Private Sub btnAceptar_Click()

If lvwEmpleados.SelectedItem Is Nothing Then

      MsgBox "Debe seleccionar al menos un empleado para el informe", vbCritical

Else

      MostrarInforme GenerarXML

End If

End Sub

 

Private Sub MostrarInforme(ByVal strXML As String)

Dim Cn As ADODB.Connection, Cm As ADODB.Command

Set Cn = New ADODB.Connection

Set Cm = New ADODB.Command

With Cn

      .Mode = adModeRead

      .CursorLocation = adUseClient

      .Open "Provider=SQLOLEDB.1;Initial Catalog=PruebasXML;Data Source=SERVIDORSQL"

End With

'// Llamar al procedimiento almacenado spInformeEmpleados, con la cadena XML como argumento

With Cm

      .ActiveConnection = Cn

      .CommandText = "spInformeEmpleados"

      .CommandType = adCmdStoredProc

      .Parameters.Append .CreateParameter("@TextoXML", adVarChar, adParamInput, 2000, strXML)

      MostrarVistaPreviaInforme .Execute

End With

Cn.Close: Set Cn = Nothing

End Sub

 

Private Function GenerarXML() As String

Dim Li As MSComctlLib.ListItem, strXML As String

'// Generar la cadena XML según el campo IdEmpleado (almacenado en la

'// propiedad Tag de cada elemento del ListView)

For Each Li In lvwEmpleados.ListItems

      If Li.Checked Then strXML = strXML & "<empleado IdEmpleado=""" & Li.Tag & """/>"

Next Li

GenerarXML = "<empleados>" & strXML & "</empleados >"

End Function

 

Private Sub MostrarVistaPreviaInforme(ByRef Rs As ADODB.Recordset)

Set DataReport1.DataSource = Rs

DataReport1.Show vbModal, frmEmpleados

Rs.Close: Set Rs = Nothing

End Sub

 

El informe resultante, utilizando un diseñador DataReport de Visual Basic 6.0, sería el siguiente:

 

 

Fig.4 – Vista previa del informe en un DataReport de Visual Basic 6.0

 


Código de ejemplo (ZIP):
 

Fichero con el código de ejemplo: firmas_ruben_argumentos_xml_sqlserver.zip - 1.09 KB

(MD5 checksum: 625AF98165AE46E05A49148FB12221F7)

 


Ir al índice principal de el Guille