Generat XML File From Oracle database.

1. Create table for insert data which can use to generate XML File. 

CREATE TABLE XML_DATA
(
  C1  VARCHAR2(10 BYTE),
  C2  VARCHAR2(100 BYTE)
)

2. Inserting data in table.

Insert into XML_DATA
   (C1, C2)
 Values
   ('100', 'KING');
Insert into XML_DATA
   (C1, C2)
 Values
   ('500', 'RAJIB');
Insert into XML_DATA
   (C1, C2)
 Values
   ('200', 'MORGAN');
COMMIT;

3. Create an directory for create an XML File

CREATE OR REPLACE DIRECTORY
BB_ICE AS
'/u01/XML/ICE/';


GRANT READ, WRITE ON DIRECTORY BB_ICE TO BEFTN;

4. Create an procedure for generate XML File.

CREATE OR REPLACE PROCEDURE DPR_GENERAT_XML_FILE
(P_DIRECTORY VARCHAR2)
IS
    V_FILE UTL_FILE.FILE_TYPE;
    V_DATA   dbms_xmlgen.ctxtype;
    v_xml       CLOB;
BEGIN
       V_FILE := utl_file.fopen (P_DIRECTORY, 'TESAT.XML', 'w');
     
    FOR I IN (SELECT C1 FROM XML_DATA) LOOP 
         V_DATA :=dbms_xmlgen.newcontext
               ('SELECT C1, C2 FROM XML_DATA WHERE C1 = '||I.C1);
         --dbms_xmlgen.setrowsettag (V_DATA, ABC);
         dbms_xmlgen.setnullhandling (V_DATA, dbms_xmlgen.EMPTY_TAG);
         dbms_xmlgen.setrowtag (V_DATA, NULL);
         v_xml := dbms_xmlgen.getxml (V_DATA);
         utl_file.put_line (V_FILE,v_xml);
         dbms_xmlgen.closecontext (V_DATA);
    END LOOP;
       utl_file.fclose   (V_FILE);  
END;