Playing with XML in Oracle Database

I posted before about storing physical  XML files in Database Table, You can read it from Here

Today I want to generate XML file from database based on certain query, then store XML file in database table.

First, I will create table to store employees XML files

 CREATE TABLE EMP_XML  
 (  
   EMPNO     NUMBER,  
   EMP_XML_FILE  XMLTYPE  
 );  

Second, I uses DBMS_XMLGEN package to generate XML file, then insert XML file in EMP_XML table

 DECLARE  
   CTX         DBMS_XMLGEN.CTXHANDLE;  
   LCLOB$XML_AS_CLOB  CLOB;  
 BEGIN  
   FOR LREC$EMPLOYEES IN (SELECT EMPNO FROM EMP)  
   LOOP  
    --Create context for XML  
    CTX :=  
      DBMS_XMLGEN.NEWCONTEXT (  
       'SELECT * FROM EMP WHERE EMPNO=' || LREC$EMPLOYEES.EMPNO);  
    --Get XML file of Context  
    LCLOB$XML_AS_CLOB := DBMS_XMLGEN.GETXML (CTX);  
    DBMS_XMLGEN.CLOSECONTEXT (CTX);  
    INSERT INTO EMP_XML (EMPNO, EMP_XML_FILE)  
       VALUES (LREC$EMPLOYEES.EMPNO, XMLTYPE (LCLOB$XML_AS_CLOB));  
   END LOOP;  
   COMMIT;  
 END;  
 /  

Third, After execute above script, execute select from EMP_XML table
SELECT * FROM EMP_XML;

The result will be like below

The structure of XML will be like below regarding to column in table

Thanks
Mahmoud A. El-Sayed