Oracle PL/SQL Generate XML - DBMS_XMLGen
The example below shows the use of DBMS_XMLGen to output Oracle database table ( CLOB ) data in XML format. The PL/SQL code will output XML data on to the terminal/screen. The code can be enhanced to output the data to a named XML file using UTIL_FILE, which requires creation of database directory. When creating output file, data volume should be in managable limits of storage systems capacity and OS memory and CPU /processor should be able to handle file I/O .

DECLARE
  v_ctx DBMS_XMLGen.ctxHandle;
  v_xml CLOB;

  PROCEDURE display_xml(in_xmldata IN OUT NOCOPY CLOB) IS
  v_xmlstr VARCHAR2(32767);
  v_xline VARCHAR2(4000);

  BEGIN
   DBMS_OUTPUT.enable(99999999);
   v_xmlstr := DBMS_LOB.SUBSTR(in_xmldata,32767);
   WHILE v_xmlstr IS NOT NULL
   LOOP
    v_xline := substr(v_xmlstr,1,instr(v_xmlstr,CHR(10))-1);
    DBMS_OUTPUT.put_line(' '||v_xline);
    v_xmlstr := substr(v_xmlstr,instr(v_xmlstr,CHR(10))+1);
   END LOOP;
  END display_xml;

BEGIN
  v_ctx := DBMS_XMLGen.newContext('SELECT q_tab.v_val month_num,
   DECODE(GREATEST(q_tab.v_val,4), q_tab.v_val,
    DECODE(GREATEST(q_tab.v_val,7), q_tab.v_val,
     DECODE(GREATEST(q_tab.v_val,10), q_tab.v_val,4,3),2),1) Quarter
  FROM (SELECT LEVEL v_val FROM DUAL CONNECT BY LEVEL <=12) q_tab');

   v_xml := DBMS_XMLGen.GetXML(v_ctx);
   DBMS_XMLGen.setRowTag(v_ctx, '');
   DBMS_XMLGen.closeContext(v_ctx);
   display_xml(v_xml);

  EXCEPTION
   WHEN OTHERS THEN
   RAISE;
  END;
  /


Example 2

Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: December 8, 2013

  1094