Utilizando XML para insertar cabeceras-detalles

Introducción

Cuantas veces debimos programar nuestras aplicaciones de manera tal que las transacciones sean manejadas por el código, por ejemplo COM +, o clases de. NET que terminan mareando no solo a quien sigue el código sino también a quien lo escribe. Por suerte SQL 2000 soporta consultas a Strings XML, por lo que si nos hacemos la idea que una cabecera y su o sus detalles pueden ser escritas en un texto XML, y si este XML lo podemos desglosar dentro de una transacción SQL, entonces nos encontramos con que tenemos una herramienta mas que poderosa frente nuestro.

Que es una Cabecera-Detalle?

Podemos decir que una cabecera-detalle es una estructura de dos(o más) tablas relacionadas de manera tal que una necesita el identificador de su “Padre” a saber:

- Una factura y sus ítems, donde los ítems necesitan el identificador de la factura.
- Un pedido de compras, donde cada artículo solicitado depende del identificador del pedido de compras.

Como regla general, en la estructura del detalle, debemos apuntar al identificador de la cabecera, para cumplir con la relación una cabecera, n detalles. Entonces la cabecera tiene sus datos que no repetiremos en el detalle, pero si el ID de la cabecera estará en el detalle.

Procedimiento normal para la inserción de cabecera-detalle

Cuando trabajamos con cabeceras y detalles debemos normalmente, escribir nuestra aplicación para que en la misma, Insertemos la cabecera, obtengamos el identificador de la misma, y luego Insertemos los detalles, y luego de chequear que todo concluyo de manera correcta cerramos la transacción DESDE LA APLICACIÓN independientemente de cerrar cada transacción SQL.

Como nos puede ayudar el XML

Con el XML, podemos hacer que la aplicación recorra los datos a Insertar (la cabecera y el / los detalles), luego mandarlo desde la aplicación a un stored procedure de SQL 2000, aprovechando las funcionalidades que posee SQL 2000 en cuanto a realizar consultas sobre texto XML, y este se encarga de transaccionar, las ventajas son implacables:

- Menos líneas de código
- Menos recursos utilizados por la aplicación
- Mucho más fácil mantenimiento.

Ejemplo Práctico

Supongamos el siguiente escenario, un instituto de enseñanza donde al dar de alta a un alumno, se genera la matricula y las cuotas a abonar.
Las estructuras son las siguientes:

CREATE TABLE [dbo].[Alumnos] (
[IdAlumno] [int] IDENTITY (1, 1) NOT NULL ,
[Nombre] [char] (50) NULL ,
[Apellido] [char] (50) NULL 
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CtaCorriente] (
[IdCtaCorriente] [int] IDENTITY (1, 1) NOT NULL ,
[IdAlumno] [int] NOT NULL ,
[NroCuota] [int] NULL ,
[Importe] [money] NULL 
) ON [PRIMARY]
GO

Si el alta del alumno y las cuotas la manejaríamos por la aplicación deberíamos:

- Crear un procedimiento de inserción de Alumno
- Crear un procedimiento de inserción de Matriculas y Cuotas

En la aplicación debemos

- Abrir la transacción desde la aplicación
- Ejecutar el alta del alumno transaccionando la ejecución de sql
- Obtener el ID del alumno
- Asignar el ID al parámetro correspondiente al ID de alumno para el detalle con su correspondiente transaccion
- Ejecutar los n detalles teniendo en cuenta que si uno falla aborte la transacción
- Si anduvo todo OK, cerrar la transacción

En XML, las cosas se simplifican ya que la aplicación deberá generar el texto XML, para ser pasado como parámetro a un stored procedure.

En base a las estructuras generaremos un alta de un alumno en este caso lo designaremos José García y las cuotas 0 (que representa la matricula) de $200 , 1, y 2 de $100 cada una.

El XML generado seria así:

<ROOT>
<Alumno Nombre="José" Apellido="García">
<Cuotas NroCuota="0" Importe="200"/>
<Cuotas NroCuota="1" Importe="100"/>
<Cuotas NroCuota="2" Importe="100"/>
</Alumno>

</ROOT>

Como Vemos dentro del tag de alumnos tenemos los datos Nombre y Apellido y dentro del tag de Cuotas tenemos el número y su importe.

Ahora como podemos explotar el XML?

Como primer paso debemos crear la representación interna del documento XML, esto se hace con el stored procedure sp_xml_preparedocument, el mismo recibe como parámetro obligatorio una variable tipo integer que devuelve el manejador del documento, por defecto llamada hDoc, y como parámetros optativos el documento XML y el esquema.

En este caso debemos ejecutar el comando de la siguiente manera:

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
N'<ROOT>
<Alumno Nombre="José" Apellido="García"> 
<Cuotas NroCuota="0" Importe="200"/> 
<Cuotas NroCuota="1" Importe="100"/> 
<Cuotas NroCuota="2" Importe="100"/>
</Alumno>
</ROOT>'

Aquí ya tenemos preparado internamente el documento, es decir SQL, tradujo el XML a una estructura interna, la cual podemos realizar consultas mediante el comando
OPENXML que recibe como parámetros, el manejador del documento (que devuelve el
sp_xml_preparedocument, o sea en nuestro caso @hDoc) y recibe también a partir de que TAG se debe realizar la consulta luego seguido de WHIT especificamos que campos queremos obtener.

El modo de ejecutarlo es:

SELECT * FROM OPENXML(@hDoc, N'/ROOT/Alumno') WITH (Nombre char(50), Apellido char(50))

Esta consulta nos devuelve:

Nombre Apellido
-------------------------------------------------- --------------------------------------------------
José García

(1 row(s) affected)

Aquí lo que hicimos es hacer un select del XML previamente preparado, a partir del tag Alumno, utilizando como representacion los campos nombre y Apellido mediante el WITH.

Ahora analizaremos el tag Cuotas

SELECT * FROM OPENXML(@hDoc, N'/ROOT/Alumno/Cuotas') with ( NroCuota int , Importe money)

Notemos que analizamos el tag Cuotas por lo tanto, el OPENXML debe referenciar al tag Cuotas (N'/ROOT/Alumno/Cuotas')

El resultado es el siguiente:

NroCuota Importe
----------- ---------------------
0             200.0000
1             100.0000
2             100.0000

(3 row(s) affected)

Para finalizar de trabajar con el documento debe ejecutarse el stored procedure sp_xml_removedocument, al cual se le envía como parámetro el manejador del documento, para que este sea removido.

Entonces, si de un archivo de texto XML, podemos obtener lo que seria la cabecera y los detalles en una misma transacción como detallamos a continuación:

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
'<ROOT>
<Alumno Nombre="José" Apellido="García">
<Cuotas NroCuota="0"
Importe="200"/>
<Cuotas NroCuota="1"
Importe="100"/> 
<Cuotas NroCuota="2"
Importe="100"/>
</Alumno>
</ROOT>'

SELECT * FROM OPENXML(@hDoc, N'/ROOT/Alumno') with (Nombre char(50) '@Nombre' , Apellido char(50)'@Apellido' )
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Alumno/Cuotas') with ( NroCuota int , Importe money)
EXEC sp_xml_removedocument @hDoc

El resultado es:

Nombre Apellido
-------------------------------------------------- --------------------------------------------------
José García

(1 row(s) affected)

NroCuota Importe
----------- ---------------------
0             200.0000
1             100.0000
2             100.0000

(3 row(s) affected)

Por lo tanto si Insertamos en la tabla de alumnos al los datos del mismo, obtenemos su ID, y lo Insertamos en el campo correspondiente de la tabla cuenta corriente, podemos hacer toda la inserción con T-SQL.

DECLARE @hDoc int
Begin Transaction
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
'<ROOT>
<Alumno Nombre="José" Apellido="García">
<Cuotas NroCuota="0"
Importe="200"/>
<Cuotas NroCuota="1"
Importe="100"/> 
<Cuotas NroCuota="2"
Importe="100"/>
</Alumno>
</ROOT>'
Declare @idAlumno int
Insert into Alumnos 
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Alumno') with (Nombre char(50), Apellido char(50))
Select @idAlumno =@@identity
If @@error <>0 
Rollback Tran
Else
Insert into CtaCorriente
SELECT @idAlumno,* FROM OPENXML(@hDoc, N'/ROOT/Alumno/Cuotas') with ( NroCuota int , Importe money)
EXEC sp_xml_removedocument @hDoc
If @@error <>0 
Rollback Tran
Else
Commit Tran

 

Refinamos un poco y creamos el stored procedure

Create Procedure AltaCabeceraDetalleXML
@XML text
as
DECLARE @hDoc int
Begin transaction
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
Declare @idAlumno int
Insert into Alumnos 
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Alumno') with (Nombre char(50) '@Nombre' , Apellido char(50)'@Apellido' )
Select @idAlumno =@@identity
If @@error <>0 
Rollback Tran
Else
Insert into CtaCorriente
SELECT @idAlumno,* FROM OPENXML(@hDoc, N'/ROOT/Alumno/Cuotas') with ( NroCuota int , Importe money)
EXEC sp_xml_removedocument @hDoc
If @@error <>0 
Begin 
Rollback Tran
Return 55000
End 
Else
Commit Tran

 

 

 

Conclusiones

Las funcionalidad XML que posee SQL 2000 puede ser utilizada para alivianar la carga de las aplicaciones tanto como en recursos de procesamiento como en tiempo de análisis y programación de transacciones que como vimos pueden ser fácilmente reemplazadas por la potencia que de por si tiene XML, combinado con la posibilidad que SQL 2000 nos brinda de utilizarlo.