XML DB Oracle, Sql Server, MySQL

matanga · #1 (**permalink**) 13/10/2008, 09:39

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.

matanga · #2 (**permalink**) 13/10/2008, 09:41

Motor: Sql Server
Versión Mínima: Sql Server 2005
Fuente:http://msdn.microsoft.com/en-us/library/ms345117.aspx http://msdn.microsoft.com/en-us/libr...7(SQL.90).aspx

1. Fichero XML

Código:

D:\xml>dir
 Volume in drive D has no label.
 Volume Serial Number is CE41-1E7C

 Directory of D:\xml

13/10/2008  12:07    <DIR>          .
13/10/2008  12:07    <DIR>          ..
13/10/2008  12:11               445 data.xml
               1 File(s)            445 bytes
               2 Dir(s)   5.093.183.488 bytes free

2. Tabla XML

Código:

create table t1
(id numeric(8),
xml_data XML)
go

3. Carga del fichero XML via SQL

Código:

insert into t1
select 1, (select * from openrowset ( bulk 'D:\xml\data.xml', single_clob) as xmlData)
go

1> select * from t1
2> go
id         xml_data

         1 <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_na
me>apellido_2</l_name><dni>dni_2</dni></entity><entity><id>3</id><f_name>nombre_
3</f_name><l_name>apellido_

(1 rows affected)

4. Ejemplos de XQuery y XPath

Código:

1> select
2> id,
3> xml_data.query('/root//entity[1]//f_name/text()') as data
4> from t1
5> go
id         data

         1 nombre_1


(1 rows affected)

5. Función Exists

Código:

1> select count(*)
2> from t1
3> where xml_data.exist('/root//entity[1]//f_name')  = 1
4> go

          1

(1 rows affected)

6. Actualizar datos

Código:

update t1
set xml_data.modify 
('replace value of (/root//entity//l_name/text())[1] with "apellido_11"')
Go

1> select
2> id,
3> xml_data.query('/root//entity[1]//l_name/text()') as data
4> from t1
5> go
id         data

         1 apellido_11

(1 rows affected)

7. Transformar una tabla a XML

Código:

create table t2
(id numeric(8),
f_name varchar(30),
l_name varchar(30),
dni varchar(30)
)
Go

insert into t2 values (1,'nombre_1','apellido_1','dni_1')
Go
insert into t2 values (2,'nombre_2','apellido_2','dni_2')
Go
insert into t2 values (3,'nombre_3','apellido_3','dni_3')
Go

7.1 Valores como atributos

Código:

select * from t2 as Entity
for xml auto
Go

<Entity id="1" f_name="nombre_1" l_name="apellido_1" dni="dni_1" />
<Entity id="2" f_name="nombre_2" l_name="apellido_2" dni="dni_2" />
<Entity id="3" f_name="nombre_3" l_name="apellido_3" dni="dni_3" />

7.2 Valores como Elementos

Código:

select id, f_name, l_name, dni
from t2
for xml path('Entity'), root('root')
Go

<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>

matanga · #3 (**permalink**) 13/10/2008, 09:43

Motor: MySQL
Versión Mínima: MySQL 5.1.5
Fuente: http://dev.mysql.com/doc/refman/5.1/...functions.html http://dev.mysql.com/tech-resources/...ql5.1-6.0.html

1. Fichero XML

Código:

D:\xml>dir
 Volume in drive D has no label.
 Volume Serial Number is CE41-1E7C

 Directory of D:\xml

13/10/2008  12:07    <DIR>          .
13/10/2008  12:07    <DIR>          ..
13/10/2008  12:11               445 data.xml
               1 File(s)            445 bytes
               2 Dir(s)   5.093.183.488 bytes free

2. Tabla XML. En el caso de MySQL, no existen (por ahora) los tipos de datos XML, por lo tanto, hay que utilizar BLOBs, cosa que no me gusta mucho, dado que las APIs que existen actualmente para el acceso a este tipo de datos son un poco limitadas.

Código:

mysql> create table t1 (id int, xml_data blob);
Query OK, 0 rows affected (0.00 sec)

3. Carga del fichero XML via SQL.

Código:

mysql> insert into t1 values (1,LOAD_FILE('d:\\xml\\data.xml'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

| id   | xml_data

|    1 | <?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>

1 row in set (0.00 sec)

4. Ejemplos de XQuery y XPath

Código:

mysql> select ExtractValue(xml_data,'root//entity[1]//l_name') from t1;
+--------------------------------------------------+
| ExtractValue(xml_data,'root//entity[1]//l_name') |
+--------------------------------------------------+
| apellido_1                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)


mysql> select id from t1
    -> where ExtractValue(xml_data,'root//entity[1]//l_name') = 'apellido_1';
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

5. No implementado en forma nativa
6. Actualizar datos

Código:

mysql> update t1 set xml_data =
    -> UpdateXML(xml_data,'root//entity[1]//l_name','<l_name>apellido_11</l_name>') ;

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;

| id   | xml_data
|    1 | <?xml version='1.0' encoding='UTF-8' ?>
<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>

1 row in set (0.00 sec)

7. Transformar una tabla a XML

Código:

mysql> create table t2
    -> (id int,
    -> f_name varchar(30),
    -> l_name varchar(30),
    -> dni varchar(30)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values (1,'nombre_1','apellido_1','dni_1');
Query OK, 1 row affected (0.69 sec)

mysql> insert into t2 values (2,'nombre_2','apellido_2','dni_2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (3,'nombre_3','apellido_3','dni_3');
Query OK, 1 row affected (0.00 sec)

7.1 Lamentablemente, en forma nativa, la única manera documentada es con el parámetro --xml del binario mysql, lo que facilita el volcado del resulset a un fichero XML, pero lo complica para enviarlo a un Recordset de cualquier lenguaje de programación. Existen UDFs de terceros que se pueden utilizar, lib_mysqludf_xql.

Código:

mysql -u root --xml=TRUE -e "SELECT * FROM xml.t1 ORDER BY id" -p xml

No publico el output, por problemas de caracteres con la consola de MS-DOS.