ORA-20000

Обычно функций xmlelement, xmlagg, xmlattributes и других подобных хватает на все случаи жизни. Но тут понадобилось создать иерархический xml на основе существующий иерархии.

Возьмем для примера существующую иерархию из схемы SCOTT:

select emp.empno, emp.ename, emp.job
from   emp
start with emp.mgr is null
connect by prior emp.empno = emp.mgr;

Для построения иерархии воспользуеся пакетом DBMS_XMLGEN. Примеры использования можно посмотреть здесь: Generating XML Using DBMS_XMLGEN. Для этого создадим функцию.

create or replace package body xml_api is

  function get_xml_hierarchy( ps_sql varchar2
                            ) return xmltype
  is
    qryctx  dbms_xmlgen.ctxhandle;
    vclob   clob;
    vresult xmltype;
  begin
    qryctx := dbms_xmlgen.newcontextFromHierarchy(ps_sql);
    vclob := dbms_xmlgen.getxml(qryctx);
    vresult := xmltype(vclob);
    dbms_xmlgen.closecontext(qryctx);
    return vresult;
  end get_xml_hierarchy;      
  
end xml_api;

Функция использует функцию dbms_xmlgen.newcontextFromHierarchy, которая принимает один параметр, это иерархический запрос. Для этого запроса есть несколько условий:

1. еще раз, это должен быть иерархический запрос;


2. результат запроса должен состоять из 2 колонок;


3. первая колонка должна быть уровнем, LEVEL


4. вторая колонка должна быть типа xmltype;

Строим xml:

select xml_api.get_xml_hierarchy(
'
select level,
       xmlelement(employee,
         xmlattributes(emp.empno, emp.ename, emp.job)) as xmlelem 
from   emp 
start with emp.mgr is null 
connect by prior emp.empno = emp.mgr
')
from   dual;

И смотрим результат:

<?xml version="1.0"?>
<EMPLOYEE EMPNO="7839" ENAME="KING" JOB="PRESIDENT">
  <EMPLOYEE EMPNO="7566" ENAME="JONES" JOB="MANAGER">
    <EMPLOYEE EMPNO="7788" ENAME="SCOTT" JOB="ANALYST">
      <EMPLOYEE EMPNO="7876" ENAME="ADAMS" JOB="CLERK"/>
    </EMPLOYEE>
    <EMPLOYEE EMPNO="7902" ENAME="FORD" JOB="ANALYST">
      <EMPLOYEE EMPNO="7369" ENAME="SMITH" JOB="CLERK"/>
    </EMPLOYEE>
  </EMPLOYEE>
  <EMPLOYEE EMPNO="7698" ENAME="BLAKE" JOB="MANAGER">
    <EMPLOYEE EMPNO="7499" ENAME="ALLEN" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7521" ENAME="WARD" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7654" ENAME="MARTIN" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7844" ENAME="TURNER" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7900" ENAME="JAMES" JOB="CLERK"/>
  </EMPLOYEE>
  <EMPLOYEE EMPNO="7782" ENAME="CLARK" JOB="MANAGER">
    <EMPLOYEE EMPNO="7934" ENAME="MILLER" JOB="CLERK"/>
  </EMPLOYEE>
</EMPLOYEE>