Generate XML file from Oracle database using pl/sql.

Hi Code seeker,

I am explaining, how to generate XML files in ORACLE database using pl/sql.

Environment Oracle database11g, Oracle Linux 5.5, windows 7, TOAD

Process 1

Below example is generate XML from only one table.

CREATE OR REPLACE DIRECTORY XML_FILES as 'D:\sale\EXCELFILE\XML_FILES';

grant read, write on  DIRECTORY  XML_FILES to alam;

create or replace procedure testxml_gen is
 rc sys_refcursor;
 doc DBMS_XMLDOM.DOMDocument;
BEGIN
 OPEN rc FOR SELECT * FROM all_objects;
 doc := DBMS_XMLDOM.NewDOMDocument(xmltype( rc ));
 DBMS_XMLDOM.WRITETOFILE(doc, 'XML_FILES/abc.xml');
END;
/

exec testxml_gen

---------------------------------------------------------------------------------------------------------------------------
Process 2

Below example is generate XML from only one table.

DECLARE
 rc sys_refcursor;
BEGIN
 OPEN rc FOR SELECT * FROM all_objects;
 dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , 'XML_FILES','clob2file.xml');
END;
/
----------------------------------------------------------------------------------------------------------------------------

Below example is generate XML from more then two tables.



CREATE OR REPLACE PROCEDURE ALAM.GEN_XML_FOR_ALL_TABLE IS
 RC SYS_REFCURSOR;
  V_DATE VARCHAR2(30);
 V_STR VARCHAR2(2000);
 V_QUERY VARCHAR2(2000);
 V_COUNT NUMBER(8);
 v_seq_value number(10);
 v_query1 varchar2(200);
v_query2 varchar2(200);
 BEGIN
 SELECT TO_CHAR(SYSDATE,'YYYYMMDDHHMISS')  INTO V_DATE FROM DUAL;
 FOR R_C1 IN (SELECT * FROM table_xml_name ) LOOP
 V_QUERY:= 'select count(*) from '|| R_C1.TABLE_NAME;
 EXECUTE IMMEDIATE V_QUERY INTO V_COUNT;
 IF V_COUNT >0 THEN
 V_STR:= 'select * from  '||R_C1.TABLE_NAME;
 OPEN RC FOR V_STR;
DBMS_XSLPROCESSOR.CLOB2FILE( XMLTYPE( RC ).GETCLOBVAL( ) , 'XML_FILES_SOURCE',(R_C1.XML_NAME||V_DATE)||'.xml');
 EXECUTE IMMEDIATE V_STR;
-- Below line is optional..
 insert into XML_MASTER_DETAIL (TABLE_NAME,XMFILE_NAME,NO_ROW,CREATE_DATE,USER_NAME) values
             (R_C1.TABLE_NAME,R_C1.XML_NAME||V_DATE||'.xml',v_count,sysdate,'ALAM');
             commit;
---------END----------------------
 ELSE
            DBMS_OUTPUT.PUT_LINE('No Data');
END IF;
 END LOOP;
 END;
/