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
——————————————————————————–
<NUM_DEPT>10</NUM_DEPT>
<NOMBRE_DEPT>ACCOUNTING</NOMBRE_DEPT>
<CIUDAD_DEPT>NEW YORK</CIUDAD_DEPT>
<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>