miércoles, agosto 03, 2005

Generación de XML mediante query.


En algunos proyectos en los que he participado, el equipo de front-end, que desarrollaba la interfaz web sobre plataforma .NET, tenía serios problemas para recuperar un conjunto de filas con un objeto tipo array incrustado.

La consulta debía devolver un vector de registros, algunos de ellos formados por una lista de valores. En aquel entonces, el driver de .NET no podía gestionar objetos UDT (tipos de datos definidos por el usuario) y hubo que comprar un componente aparte que tampoco dio buen resultado,pues no soportaba consultas a NESTED TABLES.

Una de las soluciones propuestas fue devolver el resultado (se trataba de fichas de clientes) en formato XML. La idea era buena, pero había alguno que pretendía "decodificar" columnas y filas para elaborar el resultado... ¡escribiendo un procedure con substrs!

No hace falta. La funcionalidad de crear UDT tiene una importancia relevante en las conversiones a XML. Definiendo los tipos como OBJECT y TABLE OF OBJECT podemos crear una estructura de datos facilmente exportable a XML tal y como muestra el ejemplo.

Las tablas tomadas como referencia corresponden a EMP y DEPT del esquema de ejemplo SCOTT.



SQL> create or replace type r_empleado is object(
  2     NUM_EMP          NUMBER(4),
  3     NOMBRE_EMP       VARCHAR2(10),
  4     OFICIO_EMP       VARCHAR2(9),
  5     JEFE_EMP         NUMBER(4),
  6     FCONTRATO_EMP    DATE,
  7     SALARIO_EMP      NUMBER(7,2),
  8     COMISION_EMP     NUMBER(7,2),
  9     DEPARTAMENTO_EMP NUMBER(2));
10  /

Tipo creado.

SQL>
SQL> create or replace type t_empleado is table of r_empleado;
  2  /

Tipo creado.

SQL>
SQL> create or replace type r_dept is object(
  2     NUM_DEPT     NUMBER(2),
  3     NOMBRE_DEPT  VARCHAR2(14),
  4     CIUDAD_DEPT  VARCHAR2(13),
  5     empleados    t_empleado);
  6  /

Tipo creado.

SQL>
SQL> create or replace type t_dept is table of r_dept;
  2  /

Tipo creado.

SQL>
SQL>
SQL> select sys_xmlgen(
  2               r_dept(dept.deptno, dept.dname, dept.loc,
  3                 cast(multiset(select emp.*
  4                                 from emp
  5                                where emp.deptno = dept.deptno) as t_empleado)
  6                    ),xmlformat.createFormat('DEPARTAMENTO')).getClobVal() as XML
  7  from dept
  8  where deptno=10;

XML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<DEPARTAMENTO>
<NUM_DEPT>10</NUM_DEPT>
<NOMBRE_DEPT>ACCOUNTING</NOMBRE_DEPT>
<CIUDAD_DEPT>NEW YORK</CIUDAD_DEPT>
<EMPLEADOS>
  <R_EMPLEADO>
   <NUM_EMP>7782</NUM_EMP>
   <NOMBRE_EMP>CLARK</NOMBRE_EMP>
   <OFICIO_EMP>MANAGER</OFICIO_EMP>
   <JEFE_EMP>7839</JEFE_EMP>
   <FCONTRATO_EMP>09/06/81</FCONTRATO_EMP>
   <SALARIO_EMP>2450</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7839</NUM_EMP>
   <NOMBRE_EMP>KING</NOMBRE_EMP>
   <OFICIO_EMP>PRESIDENT</OFICIO_EMP>
   <FCONTRATO_EMP>17/11/81</FCONTRATO_EMP>
   <SALARIO_EMP>5000</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7934</NUM_EMP>
   <NOMBRE_EMP>MILLER</NOMBRE_EMP>
   <OFICIO_EMP>CLERK</OFICIO_EMP>
   <JEFE_EMP>7782</JEFE_EMP>
   <FCONTRATO_EMP>23/01/82</FCONTRATO_EMP>
   <SALARIO_EMP>1300</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
</EMPLEADOS>
</DEPARTAMENTO>

3 comentarios:

Javier Morales dijo...

Hola acmeorozco,

En el proyecto en el que estuve devolvíamos el XML en una variable CLOB desde un procedure. Así, los programadores de .NET sólo se preocupaban de invocar el procedimiento y recoger el valor de la variable OUT que retornaba (también puede hacerse con una función).

Te paso páginas donde encontrarás más información sobre XML en Oracle.

una es Oracle9i XML Database Developer's Guide

y la otra es Oracle9i XML Developer's Kits Guide - XDK

Gracias, un saludo,
Javier

Anónimo dijo...

Hola Javier,
En primer lugar, gracias por tomarte el tiempo para contestar. Leeré el tema en los vínculos que has publicado y realizaré los ejercicios.
Pronto comentaré el resultado de los mismos. Mil Gracias !!!

Unknown dijo...

Buenas tardes Javier. Tengo la siguiente situacion.... En una BD tengo unsv tabla donde se almacenan xml en un campo tipo clob. Quiero extraer el xml a tablas pero todo sale en blanco.. estoy usando este query


SELECT xt.name, xt.value
FROM admstar4u.s4u1024viabilidad x,
xmltable ('/scoreRequest/requestInputTable/requestInputRow/input'
passing x.cc4u1024request
columns
name VARCHAR2(20) PATH 'scoreRequest/requestInputTable/requestInputRow/Input/@name',
value VARCHAR2(20) PATH 'scoreRequest/requestInputTable/requestInputRow/Input/@value'
) xt
WHERE x.cc4u1024nrosolicitud = '1-462963686'
and x.cc4u1024rutaejecutada='CMA'
and x.cc4u1024etapaproceso='02'
and x.cc4u1024tipoviabilidad='I';

Muchas gracias por su ayuda