Trabajar con XML en Oracle

En el artículo de hoy veremos una pequeña introducción a el uso de estructuras XML en una base de datos Oracle y como lo podemos hacer para trabajar con ella. Conoceremos algunas herramientas que nos facilita Oracle para tratar datos formateados en XML y aprenderemos a realizar las acciones básicas de inserción, selección, modificación y eliminación de datos.

Para almacenar información en formato XML en una tabla Oracle, tenemos la opción de crear atributos CLOB (Character Large Object) o XMLType. Aunque ambos son válidos y nos permiten ésta opción, si vamos a utilizarlos como simples contenedores y la gestión de información la haremos desde una aplicación externa, puede ser mejor opción crear un atributo del tipo CLOB. Los atributos CLOB guardarán nuestro XML básicamente como texto plano. En cambio, si queremos poder trabajar con el contenido de estos atributos directamente desde la base de datos, puede resultar más cómodo hacerlo con atributos del tipo XMLType, el contenido almacenado está ya preparado y listo para ser utilizado.

Puedes visitar el siguiente enlace para saber más sobre XMLType.

Vamos a comparar la creación, inserción y selección con atributos del tipo XMLType y del tipo Clob.

Creación de tablas

CREATE TABLE alumnosXMLType
( 
  id number PRIMARY KEY,
  alumno XMLType
);
/
CREATE TABLE alumnosClob
( 
  id number PRIMARY KEY,
  alumno Clob
);

Inserción de datos

INSERT INTO alumnosXMLType
     VALUES(1, XMLType('<?xml version="1.0"?>
                    <ALUMNO>
                       <NOMBRE>Aitor</NOMBRE>
                       <APELLIDOS>Díaz</APELLIDOS>
                       <DIRECCION>
                          <CALLE>Mimoses S/N</CALLE>
                          <POBLACION>San Boi de Llobregat</POBLACION>
                          <PROVINCIA>Barcelona</PROVINCIA>
                       </DIRECCION>
                       <CURSO>1</CURSO>
                    </ALUMNO>'));
/
INSERT INTO alumnosClob
     VALUES(1, '<?xml version="1.0"?>
                    <ALUMNO>
                       <NOMBRE>Aitor</NOMBRE>
                       <APELLIDOS>Díaz</APELLIDOS>
                       <DIRECCION>
                          <CALLE>Mimoses S/N</CALLE>
                          <POBLACION>San Boi de Llobregat</POBLACION>
                          <PROVINCIA>Barcelona</PROVINCIA>
                       </DIRECCION>
                       <CURSO>1</CURSO>
                    </ALUMNO>');

Hasta aquí todo es bastante parecido con la pequeña diferencia que, en el caso del XMLType, debemos crear una instancia XMLType en la inserción de datos. Pero… que ocurre si introducimos un XML mal formado?

Vamos a introducir un pequeño error en nuestro XML, a ver que sucede en ambos casos. Dejaremos sin cerrar la etiqueta «nombre».

INSERT INTO alumnosClob
     VALUES(2, '<?xml version="1.0"?>
                    <ALUMNO>
                       <NOMBRE>Aitor<NOMBRE>
                       <APELLIDOS>Díaz</APELLIDOS>
                       <DIRECCION>
                          <CALLE>Mimoses S/N</CALLE>
                          <POBLACION>San Boi de Llobregat</POBLACION>
                          <PROVINCIA>Barcelona</PROVINCIA>
                       </DIRECCION>
                       <CURSO>1</CURSO>
                    </ALUMNO>');
1 filas insertadas.

Vaya, parece que permite la introducción de XML mal formados. Vamos a intentarlo con XMLType forzando en mismo error.

INSERT INTO alumnosXMLType
     VALUES(2, XMLType('<?xml version="1.0"?>
                    <ALUMNO>
                       <NOMBRE>Aitor<NOMBRE>
                       <APELLIDOS>Díaz</APELLIDOS>
                       <DIRECCION>
                          <CALLE>Mimoses S/N</CALLE>
                          <POBLACION>San Boi de Llobregat</POBLACION>
                          <PROVINCIA>Barcelona</PROVINCIA>
                       </DIRECCION>
                       <CURSO>1</CURSO>
                    </ALUMNO>'));

Error SQL: ORA-31061: XDB error: XML event error
ORA-19202: Error occurred in XML processing
In line 11 of orastream:
LPX-00225: end-element tag "ALUMNO" does not match start-element tag "NOMBRE"

Podemos observar como, al parsearse, lo datos son validados con lo que, de esta forma, garantizamos que nuestros XML siempre estarán bien formados.

Selección de datos

La selección de datos es algo distinta a las realizadas normalmente con SQL aunque seguimos teniendo la opción de mostrar toda la información almacenada en las tablas de la forma habitual.

select * from alumnosXMLType;
/
select * from alumnosClob;

Nota: Es posible que al realizar una selección, no veamos completo todo el documento XML ya que supera el número de líneas mostradas por pantalla. Para solucionar esto, podemos utilizar el siguiente comando.

SET long 2000;

Vamos a imaginar que queremos mostrar el nombre del alumno con id=1. Debemos tener en cuenta que el nombre de las etiquetas es case sensitive con lo cual deberemos escribirlas exactamente igual como las hemos creado en nuestro tipo XMLType.

select a.alumno.extract('/ALUMNO/NOMBRE').getStringVal() 
from alumnosXMLType a
where id=1;

Nota: a es el alias que hemos añadido a la tabla alumnosXMLType.
a.alumno hace referencia al atributo alumno de la tabla alumnosXMLType, es decir, el atributo de tipo XMLType.

Con lo que obtenemos lo siguiente:

<NOMBRE>Aitor</NOMBRE>

Ya hemos obtenido el nombre pero seguramente lo que queramos sea obtener únicamente el nombre sin las etiquetas exteriores. Para hacerlo bastará con incluir «text()» en nuestra consulta para extraer únicamente el texto (contenido) entre las etiquetas Nombre.

select a.alumno.extract('/ALUMNO/NOMBRE/text()').getStringVal() 
from alumnosXMLType a
where id=1;

En este caso, el resultado es el deseado:

Aitor

Deberemos usar uno u otro caso según nos convenga.

Vamos a complicar un poco el select anterior. En este caso, queremos mostrar la población del alumno llamado Aitor que tiene el id 1.

select a.alumno.extract('/ALUMNO/DIRECCION/POBLACION/text()').getStringVal()
 from alumnosXMLType a
 where id=1 AND a.alumno.extract('/ALUMNO/NOMBRE/text()').getStringVal() = 'Aitor';

Con lo que vamos a obtener

San Boi de Llobregat

Podemos observar como en ambos selects hemos utilizado la función getStringVal() para obtener la cadena de texto incluida en el elemento seleccionado. Si no utilizáramos esta función, ambos selects nos devolverían el tipo de dato del que se trata, es decir, obtendríamos una salida como la siguiente:

(XMLTYPE)

A parte de esta función, tenemos también disponibles getClobVal() y getNumberVal() para obtener un Clob o un valor numérico respectivamente. 

Vamos a complicar un poco el select y a buscar las poblaciones de todos los alumnos que se llamen Aitor y que sean de la ciudad de Barcelona. Para hacer esto, podemos utilizar el siguiente código SQL basado en el anterior.

SELECT a.alumno.extract('/ALUMNO/DIRECCION/POBLACION/text()').getStringVal()
FROM alumnosXMLType a
WHERE a.alumno.extract('/ALUMNO/NOMBRE/text()').getStringVal() = 'Aitor' 
 AND a.alumno.extract('/ALUMNO/DIRECCION/PROVINCIA/text()').getStringVal()='Barcelona';

Podemos observar como el select empieza a aumentar de tamaño. A la hora de realizar comparaciones o condiciones con contenidos almacenados en campos XMLType, podemos utilizar la función llamada existsNode() la cual nos va a devolver 1 si se cumple la condición de su interior o, de lo contrario, nos devolverá 0. Veamos éste mismo select con la función existsNode():

SELECT a.alumno.extract('/ALUMNO/DIRECCION/POBLACION/text()').getStringVal()
FROM alumnosXMLType a
WHERE a.alumno.existsNode('/ALUMNO[NOMBRE="Aitor" and DIRECCION/PROVINCIA="Barcelona"]')=1;

Podemos ver como la consulta es mucho mas compacta.

Modificación y actualización de registros

Podemos actualizar de forma rápida y sencilla todo un registro XML utilizando la sentencia UPDATE.

Vamos a modificar todos los datos de los alumnos cuyo nombre empieza por una «A» y termine por una «r». Para ello, realizaremos la siguiente sentencia:

UPDATE alumnosXMLType a
   SET  a.alumno = XMLType('<?xml version="1.0"?>
                    <ALUMNO>
                       <NOMBRE>Aitor</NOMBRE>
                       <APELLIDOS/>
                       <DIRECCION>
                          <CALLE>Bonavista S/N</CALLE>
                          <POBLACION>Cornella de Llobregat</POBLACION>
                          <PROVINCIA>Barcelona</PROVINCIA>
                       </DIRECCION>
                       <CURSO>1</CURSO>
                    </ALUMNO>')
WHERE a.alumno.EXTRACT('/ALUMNO/NOMBRE/text()').getStringVal() like 'A%r';

Como podéis observar, de esta forma estamos sobreescribiendo los campos «alumno» de la tabla alumnosXMLType que cumplen la condición mencionada. Pero ¿cómo lo debemos hacer si únicamente queremos modificar el contenido de una etiqueta?

Vamos a modificar la provincia del alumno llamado Aitor. La nueva provincia será Girona. El resto del contenido no debe ser modificado. Para ello, vamos a utilizar la función UPDATEXML() que nos permite modificar partes concretas de un documento XML dado.

UPDATE alumnosXMLType a
SET alumno =  UPDATEXML(alumno,'//DIRECCION/PROVINCIA/text()', 'Girona') 
WHERE a.alumno.EXTRACT('//NOMBRE/text()').getStringVal() = 'Aitor';

A partir de ahora, el alumno Aitor pertenece a la provincia de Girona. Fijaros además que podemos obviar la etiqueta raíz (ALUMNO) tanto en la función UPDATEXML como en la función EXTRACT.

Finalmente, vamos a dejar el alumno tal y como estaba hace un momento, para ello, realizaremos un update para asignar al alumno llamado Aitor la población de «Sant Boi de Llobregat» y la provincia de «Barcelona».

UPDATE alumnosxmltype a
SET alumno =  UPDATEXML(alumno, 
'//DIRECCION/PROVINCIA/text()', 'Barcelona',
'//DIRECCION/POBLACION/text()', 'Sant Boi de Llobregat'
) 
WHERE a.alumno.existsNode('/ALUMNO[NOMBRE="Aitor"]')=1;

Tras esta actualización, ya tenemos el alumno nuevamente en Sant Boi de Llobregat provincia de Barcelona.

Eliminación de registros

Para terminar, vamos a eliminar el alumno llamado «Aitor» dejando al resto sin alterar. Para ello, debemos usar la sentencia DELETE.

DELETE FROM alumnosXMLType a
WHERE a.alumno.extract('/ALUMNO/NOMBRE/text()').getStringVal()='Aitor';

Hasta aquí la introducción a Oracle con XML. En el próximo artículo veremos como podemos trabajar con tablas XML en Oracle desde una aplicación escrita en lenguaje Java.