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

Importar un XML a una tabla Oracle

CREATE OR REPLACE procedure p_importafromxml(              --se crea el procedimiento

                    pv_tabla  varchar2,                    --se crean los parametros                                          

                    pv_esquema varchar2) is

l_bfile   BFILE;                                                                    --se crea el archivo bfile                                                                               

vv_esquema_tabla varchar2(2000);                                       --se crea la variable esquema tabla

vc_xml clob;                                                                         --se crea la variable clob

vn_registros number;                                                      --se crea la variable de los registros

ctx dbms_xmlsave.ctxtype;                                         --se crea la variable xmlsave

begin                                                                             --inicio del bloque

dbms_lob.createtemporary(vc_xml, cache=>FALSE);   

--se crea un lob.temporary que aproveche todo el clob

  l_bfile := BFileName('XMLEXPORT','datos.xml');                                                 

--a la variable clob se le asigna bfilename (directorio,archivo)

 

  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);                                         

--se hace un lob.open(variable bfile, lob.readonly)

  dbms_lob.loadFromFile(dest_lob => vc_xml,                                                    

--lob.loadfromfile con los parametros correspondientes

                        src_lob  => l_bfile,

                        amount   => dbms_lob.getLength(l_bfile));                             

--lob.getlength (bfile)

  dbms_lob.close(l_bfile);                                 --lob.close(bfile)

   

vv_esquema_tabla:=pv_esquema||'.'||pv_tabla;                                                 

--esquema + tabla

ctx:=dbms_xmlsave.newcontext(vv_esquema_tabla);                                      

--a la variable contexto se le asigna uno nuevo contexto(esquema.tabla)

dbms_xmlsave.setrowtag(ctx,'DATA_RECORD');                                              

--se setea fila y etiqueta(contexto, primera etiqueta del xml)

vn_registros:=dbms_xmlsave.insertxml(ctx,vc_xml);                                         

--a la variable registros se le asigna el xmlsave.insert(contexto,variable clob)

dbms_xmlsave.closecontext(ctx);                       --se cierra el contexto

end;                                                                      --fin del bloque

end p_importafromxml;                                           --fin del procedimiento