XMLAGG con DBMS_XMLDOM

CREATE OR REPLACE PROCEDURE P_XMLDOMAGG IS                                  

CURSOR xmlcur IS                              --se crea el cursor                                                      

SELECT XMLELEMENT (                                                  --se inicia el elemento padre

                        "Divisoft", xmlagg(                                 --se inicia la agrupacion de dep

(SELECT XMLAGG (XMLELEMENT (                                  --se inicia la agrupacion

          "Department",

          XMLAttributes (d.department_name AS "name"),        --se crea el atributo

          (SELECT XMLAGG (                                              --se inicia la agrupacion

                     XMLELEMENT (                                         --se crea un elemento

                        "emp",                                                  

                        XMLAttributes (e.last_name AS name),                --se crea un atributo

                        (SELECT XMLAGG (                                            --se inicia la agrupacion

                                   XMLELEMENT (                           --se crea un elemento

                                      "dependent",   

                                      XMLAttributes (de.name AS "name")))   --se crea un elemento

                           FROM dependents de                           --tabla

                          WHERE de.employee_id = e.employee_id                      --clausula

                          )))

             FROM employees e                               --tabla

            WHERE e.department_id = d.department_id        --clausula

            and  exists(select 1 from dependents z WHERE z.employee_id = e.employee_id)

            )))        --subconsulta

          AS "dept_list"                                                       --alias de la subconsulta

  FROM departments d                                                    --tabla principal

   ))) Planilla                                                                  --alias del query

   from dual;                                                                  --tabla dual

  

XMLDATA XMLTYPE;                                                      --variable xmltype

doc  dbms_xmldom.domdocument;                                        --variable dom

                                 

                                                                                                                   

   BEGIN                                                                       --inicio del bloque

     OPEN xmlcur;                                                                          --se abre el cursor    

     FETCH XMLCUR INTO XMLDATA;                  --se lee el cursor y se mete en la variable xmltype

     CLOSE xmlcur;                                              --cerrar el cursor

     doc  := dbms_xmldom.newdomdocument(XMLDATA); --nuevo documento

     dbms_xmldom.writetofile(doc, 'XMLEXPORT/EMPRESA.xml');     --escribe en el archvo                                                                        

   END;                                                  --fin del bloque

END;                                                     --fin del procedimiento