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>
Javier Morales, he leído el artículo y el ejemplo lo he ejecutado y ha funcionado correctamente. Sin embargo, me encuentro en una situación parecida a la descrita en el encabezado del artículo. Quisiera conseguir mayor información acerca del funcionamiento de XML en ORACLE. ¿Qué sitio o lecturas me puedes recomendar? Gracias.
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
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 !!!