En vista de algunas preguntas sobre como integrar XML y bases de datos, es que posteo este mini how-to.
El alcance del post es cargar, leer, actualizar y transformar datos desde y hacia XML dentro de la base de datos, por lo tanto, no incluye ningún tipo de operaciones desde un provider o driver hacia arriba.
Motor: Oracle
Versión Mínima: Oracle9i release 1 (9.0.1)
Fuente:
http://download.oracle.com/docs/cd/B...b14259/toc.htm
1. Empezamos con un pequeño fichero XML almacenado en disco.
Código:
oracle@buo:~/scripts/xml> more data.xml
<?xml version='1.0' encoding='UTF-8' ?>
<root>
<entity>
<id>1</id>
<f_name>nombre_1</f_name>
<l_name>apellido_1</l_name>
<dni>dni_1</dni>
</entity>
<entity>
<id>2</id>
<f_name>nombre_2</f_name>
<l_name>apellido_2</l_name>
<dni>dni_2</dni>
</entity>
<entity>
<id>3</id>
<f_name>nombre_3</f_name>
<l_name>apellido_3</l_name>
<dni>dni_3</dni>
</entity>
</root>
2. Creación de la tabla, con un identificador y una columna del tipo XMLType.
Código:
SQL> create table t1
2 (id number(8),
3 xml_data XMLType)
4 /
Table created.
3. Para la carga del fichero, opté por hacerlo vía SQL o PL/SQL.
Código:
SQL> create directory xmldir as '/home/oracle/scripts/xml'
2 /
Directory created.
SQL> insert into t1 values (1,XMLType(bfilename('XMLDIR','data.xml'),nls_charset_id('UTF8')));
1 row created.
SQL> select id, xml_data from t1;
ID
----------
XML_DATA
-----------------------------------------
1
<?xml version="1.0" encoding="US-ASCII"?>
<root>
<entity>
<id>1</id>
<f_name>nombre_1</f_name>
<l_name>apellido_1</l_name>
<dni>dni_1</dni>
</entity>
<entity>
<id>2</id>
<f_name>nombre_2</f_name>
<l_name>apellido_2</l_name>
<dni>dni_2</dni>
</entity>
<entity>
<id>3</id>
<f_name>nombre_3</f_name>
<l_name>apellido_3</l_name>
<dni>dni_3</dni>
</entity>
</root>
4. Los ejemplos básicos de XQuery y XPath, en primer lugar un select del texto que contiene el Elemento L_NAME dentro del XPath root/entity[1]/l_name, en segundo lugar, la misma función pero dentro de un WHERE
Código:
SQL> select extractValue(xml_data,'root/entity[1]/l_name') from t1;
EXTRACTVALUE(XML_DATA,'ROOT/ENTITY[1]/L_NAME')
--------------------------------------------------------------------------------
apellido_1
SQL> select id from t1
2 where extractValue(xml_data,'root/entity[2]/l_name') = 'apellido_2';
ID
----------
1
5. Función muy útil para evaluar la existencia de un Elemento, existsNode devuelve un 1 por true y 0 por false.
Código:
SQL> select count(*) from t1
2 where existsNode(xml_data,'root/entity/l_name') = 1;
COUNT(*)
----------
1
6. Actualizar el texto dentro del Elemento L_NAME en la primera posición de la colección de Elementos Entity, aquí mismo es donde se empieza a notar la potencia de XML DB, dado que en una sola sentencia SQL podemos actualizar un nodo dentro de un XML en un registro de una tabla, antes, debíamos extraer todo el XML, hacerlo pasar por una capa de acceso a datos, parsearlo mediante código, actualizar el valor y guardar nuevamente el XML en la base de datos.
Código:
SQL> update t1
2 set xml_data = updateXML(xml_data,'root/entity[1]/l_name/text()','apellido_11')
3 where extractValue(xml_data,'root/entity[1]/l_name') = 'apellido_1';
1 row updated.
SQL> select id, xml_data from t1;
ID
----------
XML_DATA
--------------------------------------------------------------------------------
1
<?xml version="1.0" encoding="US-ASCII"?>
<root>
<entity>
<id>1</id>
<f_name>nombre_1</f_name>
<l_name>apellido_11</l_name>
<dni>dni_1</dni>
</entity>
<entity>
<id>2</id>
<f_name>nombre_2</f_name>
<l_name>apellido_2</l_name>
<dni>dni_2</dni>
</entity>
<entity>
<id>3</id>
<f_name>nombre_3</f_name>
<l_name>apellido_3</l_name>
<dni>dni_3</dni>
</entity>
</root>
7. Vamos por el paso inverso, transformar una tabla estándar con varias columnas al formato XML.
Código:
SQL> create table t2
2 (id number(8),
3 f_name varchar2(30),
4 l_name varchar2(30),
5 dni varchar2(30)
6 )
7 /
Table created.
SQL> insert into t2 values (1,'nombre_1','apellido_1','dni_1')
2 /
1 row created.
SQL> insert into t2 values (2,'nombre_2','apellido_2','dni_2')
2 /
1 row created.
SQL> insert into t2 values (3,'nombre_3','apellido_3','dni_3')
2 /
1 row created.
7.1 Formato XML donde cada columna esta representada como atributos.
Código:
SQL> select XMLElement("Entity", XMLAttributes(
2 id as "ID",
3 f_name as "F_NAME",
4 l_name as "L_NAME",
5 dni as "DNI"))
6 as xml
7 from t2;
XML
------------------------------------------------------------------------------
<Entity ID="1" F_NAME="nombre_1" L_NAME="apellido_1" DNI="dni_1"></Entity>
<Entity ID="2" F_NAME="nombre_2" L_NAME="apellido_2" DNI="dni_2"></Entity>
<Entity ID="3" F_NAME="nombre_3" L_NAME="apellido_3" DNI="dni_3"></Entity>
7.2 Formato XML donde cada columna esta representada como elementos.
Código:
SQL> select XMLElement("Entity",XMLForest(id, f_name, l_name, dni)) as xml from t2;
XML
----------------------------------------------------------------------------------------------------
<Entity><ID>1</ID><F_NAME>nombre_1</F_NAME><L_NAME>apellido_1</L_NAME><DNI>dni_1</DNI></Entity>
<Entity><ID>2</ID><F_NAME>nombre_2</F_NAME><L_NAME>apellido_2</L_NAME><DNI>dni_2</DNI></Entity>
<Entity><ID>3</ID><F_NAME>nombre_3</F_NAME><L_NAME>apellido_3</L_NAME><DNI>dni_3</DNI></Entity>
La construccion del fichero XML queda a gusto de cada uno, hay muchos post donde se comenta como transformar un resultset a fichero.