04 June, 2012

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

5 comments:

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...