01 June, 2012

Store Physical XML Files in Database Table

I have XML files stores as physical files and I want to store these file on database table.

Scenario
1- Create a table for storing XML files
2- Create Directory object in database to refer to path of XML files
3- Use my previous post List Contents of Directory to list all files in directory
4- Read every XML file and store it on database table



1-Create a table for storing XML files
I will create table for storing XML files
 CREATE TABLE SCOTT.XML_TABLE  
 (  
  XML_ID  INTEGER,  
  XML_FILE XMLTYPE  
 );  

2- Create Directory object in database to refer to path of XML files
Suppose that all my XML files are existed in my machine at D:\XML
 CREATE OR REPLACE DIRECTORY XML_DIR AS 'D:\XML';  

3- Use my previous post List Contents of Directory to list all files in directory
Suppose that all my XML files are existed in my machine at D:\XML .
In my previous post List List Contents of Directory I created get_directory_content function which I will use it to list XML files in D:\XML directory
 SELECT * FROM TABLE (get_directory_content ('D:\XML', 'Y'));  

The output will be






4- Read every XML file and store it on database table
Before I run my code I should grant java read permission on D:\XML directory
So I should run below script first
 BEGIN  
   DBMS_JAVA.grant_permission ('SCOTT',  
                 'SYS:java.io.FilePermission',  
                 '<<ALL FILES>>',  
                 'read');  
 END;  


Now let's run below code to insert XML files in folder D:\XML into XML_TABLE table
 DECLARE  
   LN$COUNTER   NUMBER := 1;  
   LC$FILE_NAME  VARCHAR2 (255);  
 BEGIN  
   SELECT NVL (MAX (XML_ID) + 1, 1) INTO LN$COUNTER FROM XML_TABLE;  
   FOR MY_REC  
    IN (SELECT * FROM TABLE (GET_DIRECTORY_CONTENT ('D:\XML', 'Y')))  
   LOOP  
    LC$FILE_NAME :=  
      SUBSTR (MY_REC.COLUMN_VALUE,  
          INSTR (MY_REC.COLUMN_VALUE, '\', -1) + 1);  
    INSERT INTO XML_TABLE (XML_ID, XML_FILE)  
       VALUES (  
            LN$COUNTER,  
            XMLTYPE (BFILENAME ('XML_DIR', LC$FILE_NAME),  
                NLS_CHARSET_ID ('AL32UTF8')));  
    LN$COUNTER := LN$COUNTER + 1;  
   END LOOP;  
 END;  

To query from XML_TABLE table use below query
 SELECT T1.XML_ID, T1.XML_FILE.GETSTRINGVAL () AS XML_DATA  
  FROM XML_TABLE T1;  

The output in sqlplus is like below


Thanks
Mahmoud A. El-Sayed
Recommended Post Slide Out For Blogger