laboratorio de xml en db2

Universidad Cenfotec Maestría en Base de datos

Curso: Programación avanzada de base de datos

Profesor: Mario Chacón

Investigación

Laboratorio: XML en Base de datos

Alumno

López Portillo, José Ahias

Abril del 2013

Laboratorio de XML en base de datos DB2

Objetivo El presente laboratorio tiene como objetivo, proveer los conocimientos mínimos para el manejo de datos

XML en DB2, para ello se presentan una serie de indicaciones y se proveen script para comprender el

funcionamiento de XML en DB2.

Prerrequisitos DB2 Express-C 10.5 for Windows 64-bit

Data Studio Administration Client

Carpeta de Script de laboratorio.

Desarrollo 1. Ingresar a Data Studio

2. Crear una nueva base de datos

3. Ingresar a sección de esquemas

4. Seleccionar el esquema “DB2ADMIN”

5. Seleccionar Nuevo Query.

6. Ejecutar el siguiente Script para crear Tabla para el desarrollo del laboratorio

CREATE TABLE Solicitudes

(

CustomerID int,

Id_Solicitud int,

Id_TipoPrestamo int,

monto decimal(24,6),

XML_Detalle xml

) ;

7. Realizar el siguiente INSERT

INSERT INTO Solicitudes(CustomerID,

Id_Solicitud,

Id_TipoPrestamo,

monto,

XML_Detalle)

VALUES(1,

1,

2,

23456.10,

XMLPARSE(document'<?xml version="1.0" encoding="utf-8"?>

<solicitudes>

<solicitud>

<idSolicitud>1</idSolicitud>

<idCliente>11701</idCliente>

<tipoCliente>4551</tipoCliente>

<datosPersonales>

<nombre>Pedro</nombre>

<direccion>Heredia</direccion>

<telefono>78010101</telefono>

</datosPersonales>

<numeroCuentaCliente>001-001-001</numeroCuentaCliente>

<montoAhorro>999999.99</montoAhorro>

<IdTipoPrestamo>2</IdTipoPrestamo>

<tipoPrestamo>Hipotecario</tipoPrestamo>

<montoMinimo>99999999999.00</montoMinimo>

<datosPrestamos>

<propertyID>101010</propertyID>

<propertyTypeID>1</propertyTypeID>

</datosPrestamos>

<fiadores>

<fiador>

<idClienteFiador>2525</idClienteFiador>

<numeroCuentaFiador>0808-0808-08</numeroCuentaFiador>

</fiador>

</fiadores>

</solicitud>

</solicitudes>' preserve whitespace)

);

Nota: El query anterior realiza un insert en la tabla de solicitudes, para poder realizar el insert es necesario convertir el string con format XML, por ello se utiliza la función XMLPARSE que retorna un tipo de datos XML valido. Realizar un query para validar el insert SELECT *

FROM Solicitudes;

8. En la siguiente consulta, se retorna el registro insertado, pero se realizara una conversión del

campo XML a un string, para ello se utilizara la función XMLSERIALIZE.

SELECT CustomerID,

Id_Solicitud,

Id_TipoPrestamo,

monto,

XMLSERIALIZE(XML_Detalle AS VARCHAR(4000)) AS XMLString

FROM Solicitudes;

9. En el siguiente ejercicio crearemos un procedimiento almacenado que reciba un parámetro XML.

El parámetro de entrada será utilizado para consultar información interna de su XML para ser

insertado en la tabla, para ello se utilizara XQUERY para consultar información del XML y luego

esa información será convertida en el tipo de dato según tabla por medio de la función de

XMLCAST.

Ingresar a la sección de StoreProcedure

Completar informacion de asistente

Presionar el botón finalizar, el asistente agregara código por defecto.

Sustituir el código autogenerado por el siguiente código CREATE PROCEDURE spInsertSolicitud (

IN inpdoc XML

)

DYNAMIC RESULT SETS 1

P1: BEGIN

DECLARE CustomerID INT;

DECLARE Id_Solicitud INT;

DECLARE Id_TipoPrestamo INT;

DECLARE Monto decimal(24,6);

VALUES XMLCAST

(

XMLQUERY('$d/solicitudes/solicitud/idSolicitud' PASSING inpdoc AS "d")

AS INT

)

INTO Id_Solicitud;

VALUES XMLCAST

(

XMLQUERY('$d/solicitudes/solicitud/idCliente' PASSING inpdoc AS "d")

AS INT

)

INTO CustomerID;

VALUES XMLCAST

(

XMLQUERY('$d/solicitudes/solicitud/IdTipoPrestamo' PASSING inpdoc AS "d")

AS INT

)

INTO Id_TipoPrestamo;

VALUES XMLCAST

(

XMLQUERY('$d/solicitudes/solicitud/montoAhorro' PASSING inpdoc AS "d")

AS decimal(24,6)

)

INTO Monto;

INSERT INTO solicitudes(CustomerID,Id_Solicitud,Id_TipoPrestamo,monto,XML_Detalle)

VALUES(CustomerID,Id_Solicitud,Id_TipoPrestamo,Monto, inpdoc);

END P1

Realizar el Deploy del procedimiento almacenado

Para probar el procedimiento almacenado ejecutar el siguiente query CALL spInsertSolicitud (

XMLPARSE(

DOCUMENT

'<solicitudes>

<solicitud>




<datosPersonales>

<nombre>Pedro</nombre>

<direccion>Heredia</direccion>


</datosPersonales>






<datosPrestamos>



</datosPrestamos>

<fiadores>

<fiador>



</fiador>

</fiadores>

</solicitud>

</solicitudes>'

)

);

SELECT *

FROM solicitudes

WHERE ID_SOLICITUD=2;

10. El siguiente query, crea una función que retorna un registro relacional que es obtenida de los

registros almacenados en los XML, para ello se utilizara función de db2 XMLTABLE, que

transforma el campo XML en campos relacionales, los campos son mapeados por medio XPATH,

al hacer referencia a la ruta especifica según el documento XML almacenado.

CREATE FUNCTION fnGetSolicitudes (pSolicitud INTEGER)

RETURNS TABLE (

idSolicitud INTEGER ,

idCliente INTEGER ,

tipoCliente INTEGER ,

nombre VARCHAR(300),

direccionPersona VARCHAR(300),

telefono VARCHAR(300),

representanteLegal VARCHAR(300),

direccionRepresentante VARCHAR(300),

telefonoRepresentante VARCHAR(300),

numeroCuentaCliente VARCHAR(300),

montoAhorro DECIMAL(24,6),

IdTipoPrestamo INTEGER ,

tipoPrestamo VARCHAR(300),

montoMinimo DECIMAL(24,6),

propertyID INTEGER ,

propertyTypeID INTEGER ,

idClienteFiador INTEGER ,

numeroCuentaFiador VARCHAR(300)

)

LANGUAGE SQL

READS SQL DATA

NO EXTERNAL ACTION

DETERMINISTIC

RETURN

SELECT X.* FROM

XMLTABLE ('$d/solicitudes/solicitud' passing

(SELECT XML_DETALLE

from SOLICITUDES

WHERE ID_SOLICITUD=pSolicitud) as "d"

COLUMNS

idSolicitud INTEGER PATH 'idSolicitud',

idCliente INTEGER PATH 'idCliente',

tipoCliente INTEGER PATH 'tipoCliente',

nombre VARCHAR(300) PATH 'datosPersonales/nombre',

direccionPersona VARCHAR(300) PATH 'datosPersonales/direccion',

telefono VARCHAR(300) PATH 'datosPersonales/telefono',

representanteLegal VARCHAR(300) PATH 'datosAdministrativo/representanteLegal',

direccionRepresentante VARCHAR(300) PATH 'datosAdministrativo/direccion',

telefonoRepresentante VARCHAR(300) PATH 'datosAdministrativo/telefono',

numeroCuentaCliente VARCHAR(300) PATH 'numeroCuentaCliente',

montoAhorro DECIMAL(24,6) PATH 'montoAhorro',

IdTipoPrestamo INTEGER PATH 'IdTipoPrestamo',

tipoPrestamo VARCHAR(300) PATH 'tipoPrestamo',

montoMinimo DECIMAL(24,6) PATH 'montoMinimo',

propertyID INTEGER PATH 'datosPrestamos/propertyID',

propertyTypeID INTEGER PATH 'datosPrestamos/propertyTypeID',

idClienteFiador INTEGER PATH 'fiadores/fiador/idClienteFiador',

numeroCuentaFiador VARCHAR(300) PATH 'fiadores/fiador/numeroCuentaFiador'

) AS X

Para poder utilizar la función ejecutar el siguiente comando SELECT *

FROM TABLE( fnGetSolicitudes(1)) AS xmlTableSolicitud;

11. En este ejercicio se registrar un esquema XML que define la correcta estructura de un documento

XML, al realizar el insert se utilizara el comando XMLVALIDATE que valida un documento XML y

retorna un documento que es completamente valido.

Esquema XSD

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="solicitudes"> <xs:complexType> <xs:sequence> <xs:element name="solicitud"> <xs:complexType> <xs:sequence> <xs:element type="xs:byte" name="idSolicitud"/> <xs:element type="xs:short" name="idCliente"/> <xs:element type="xs:byte" name="tipoCliente"/> <xs:element name="datosPersonales"> <xs:complexType> <xs:sequence> <xs:element type="xs:string" name="nombre"/> <xs:element type="xs:string" name="direccion"/> <xs:element type="xs:int" name="telefono"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element type="xs:string" name="numeroCuentaCliente"/> <xs:element type="xs:float" name="montoAhorro"/> <xs:element type="xs:byte" name="IdTipoPrestamo"/> <xs:element type="xs:string" name="tipoPrestamo"/> <xs:element type="xs:float" name="montoMinimo"/> <xs:element name="datosPrestamos"> <xs:complexType> <xs:sequence> <xs:element type="xs:int" name="propertyID"/> <xs:element type="xs:byte" name="propertyTypeID"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="fiadores"> <xs:complexType> <xs:sequence> <xs:element name="fiador"> <xs:complexType> <xs:sequence> <xs:element type="xs:short" name="idClienteFiador"/> <xs:element type="xs:string" name="numeroCuentaFiador"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element>

</xs:schema>

Registrando esquema XSD en DB2

Ejecutar el siguiente INSERT, utilizando la validación del esquema INSERT INTO Solicitudes(CustomerID,

Id_Solicitud,

Id_TipoPrestamo,

monto,

XML_Detalle)

VALUES(587,

10,

3,

9.99,

XMLVALIDATE(


<solicitudes>

<solicitud>




<datosPersonales>

<nombre>PRUEBA</nombre>

<direccion>SAN JOSE</direccion>


</datosPersonales>






<datosPrestamos>



</datosPrestamos>

<fiadores>

<fiador>



</fiador>

</fiadores>

</solicitud>

</solicitudes>' )

ACCORDING TO XMLSCHEMA ID ESQUEMA_SOLICITUDES)

);

Verificar registro insertado SELECT *

FROM solicitudes

WHERE ID_SOLICITUD=10;

Intentaremos insertar un registro que no concuerde con el esquema XSD, para ello modificaremos el insert anteriormente realizado, cambiaremos el TAG <solicitud> por <solicitud2> al realizar esto el insert debe fallar. Ejecutar la siguiente sentencia. INSERT INTO Solicitudes(CustomerID,

Id_Solicitud,

Id_TipoPrestamo,

monto,

XML_Detalle)

VALUES(587,

10,

3,

9.99,

XMLVALIDATE(


<solicitudes>

<solicitud2>




<datosPersonales>

<nombre>PRUEBA</nombre>

<direccion>SAN JOSE</direccion>


</datosPersonales>






<datosPrestamos>



</datosPrestamos>

<fiadores>

<fiador>



</fiador>

</fiadores>

</solicitud2>

</solicitudes>' )

ACCORDING TO XMLSCHEMA ID ESQUEMA_SOLICITUDES)

);

laboratorio de xml en db2

Education