Loading XML file into Oracle tables

There are various ways to load XML files into Oracle tables. Here I am going to show a very simple example to load Employee data from SCOTT schema to a table called “TEMP”.

For doing this we need to have a XML file first, here the below code will export the table data into a XML file:


declare
ctx dbms_xmlgen.ctxtype;
begin
ctx := dbms_xmlgen.newcontext ('select * from emp');
dbms_xslprocessor.clob2file (dbms_xmlgen.getxml (ctx), 'MY_DIR', 'emp.xml');
dbms_xmlgen.closecontext (ctx);
end;
/ 


The above code will generate a XML file on my directory i.e. MY_DIR.

To create a directory you can follow the below steps:

Run the below code under SYS user:


SQL> create directory my_dir as 'C:\Users\Sloba\Desktop\XML'; 


Grant permissions on directory and UTL_FILE to scott so that the PLSQL block for exporting XML file will work successfully.


SQL> grant read, write, execute on directory my_dir to scott;

SQL> grant execute on UTL_FILE to SCOTT;

SQL> grant execute on DBMS_XMLGEN to scott;


Once the XML file is been generated then create a table called “TEMP” on scott schema as below:


SQL> create table temp as select * from emp where 1=2;


The above DDL will create the table similar to emp table but without any records.

Now run the below query to check if you are able to access the XML file :


SQL> SELECT xmltype(bfilename('MY_DIR', 'emp.xml'), nls_charset_id('UTF-8')) xmlcol FROM dual; 


image

Now let us run the below query and check if we are getting the records what we get when we do a select on EMP table or not:


SQL> 

WITH t AS (SELECT xmltype(bfilename('MY_DIR', 'emp.xml'), nls_charset_id('UTF-8')) xmlcol FROM dual)
SELECT
extractValue(value(x),'/ROW/EMPNO') EMPNO
,extractValue(value(x),'/ROW/ENAME') ENAME 
,extractValue(value(x),'ROW/JOB') JOB
,EXTRACTVALUE(value(X),'ROW/MGR') MGR
,EXTRACTVALUE(value(X),'ROW/HIREDATE') HIREDATE
,EXTRACTVALUE(value(X),'ROW/SAL') SAL
,EXTRACTVALUE(value(X),'ROW/COMM') COMM
,EXTRACTVALUE(value(X),'ROW/DEPTNO') DEPTNO
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/ROWSET/ROW'))) x;  


The result will be as below:

image

Finally to insert the same on the temp table we can use a simple insert statement as like below:


SQL> 

insert into TEMP 
WITH t AS (SELECT xmltype(bfilename('MY_DIR', 'emp.xml'), nls_charset_id('UTF-8')) xmlcol FROM dual)
SELECT
extractValue(value(x),'/ROW/EMPNO') EMPNO
,extractValue(value(x),'/ROW/ENAME') ENAME 
,extractValue(value(x),'ROW/JOB') JOB
,EXTRACTVALUE(value(X),'ROW/MGR') MGR
,EXTRACTVALUE(value(X),'ROW/HIREDATE') HIREDATE
,EXTRACTVALUE(value(X),'ROW/SAL') SAL
,EXTRACTVALUE(value(X),'ROW/COMM') COMM
,EXTRACTVALUE(value(X),'ROW/DEPTNO') DEPTNO
FROM t,TABLE(XMLSequence(extract(t.xmlcol,'/ROWSET/ROW'))) x;  

commit;


So finally do a select on TEMP table and the result will be as like below:

image

Advertisements

4 thoughts on “Loading XML file into Oracle tables

    • Try something like this:

      SQL> select * from all_directories;

      OWNER DIRECTORY_NAME DIRECTORY_PATH
      —————————— —————————— ——————————————————————————–
      SYS ORA_DIR E:\ORA_DIR

      SQL>

      CREATE TABLE myxml_file (ID NUMBER, MYXMLFILE BFILE);

      INSERT INTO myxml_file VALUES (1, BFILENAME(‘ORA_DIR’, ‘sample.xml’));
      commit;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s