Load XML File in Oracle Table

In my last post, I have created a XML file using PL/SQL. Here we are trying to load that XML data back in a normal table of Oracle Database. We assume that the directory is created and the permissions are already granted also the XML file has been exported. To read my last post on Exporting XML file using PL/SQL, creating directory and granting permission, please click Create XML file using PL/SQL

To Load "EMP_DEPT.XML" file in to Oracle Table we have created a table with same structure of XML file EMP_DEPT as

C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:47:05 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> CREATE TABLE EMP_DEPT
  2  (
  3  EMPNO NUMBER(4),
  4  ENAME VARCHAR2(10),
  5  SAL NUMBER(7,2),
  6  DNAME VARCHAR2(14)
  7  );

Table created.

Once the table is created, we just need to execute following query to load the XML file into the Oracle table.

SQL> INSERT INTO EMP_DEPT (EMPNO, ENAME, SAL, DNAME)
  2  SELECT *
  3  FROM XMLTABLE('/ROWSET/ROW'
  4         PASSING XMLTYPE(BFILENAME('MYXML', 'EMP_DEPT.XML'),
  5         NLS_CHARSET_ID('CHAR_CS'))
  6         COLUMNS EMPNO  NUMBER(4)    PATH 'EMPNO',
  7                 ENAME  VARCHAR2(10) PATH 'ENAME',
  8                 SAL    NUMBER(7,2)  PATH 'SAL',
  9                 DNAME  VARCHAR2(14) PATH 'DNAME'
10       )
11  ;
14 rows created.

Data of "EMP_DEPT" can be determind as following

SQL> SELECT * FROM EMP_DEPT;
     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7782 CLARK            2450 ACCOUNTING
      7839 KING             5000 ACCOUNTING
      7934 MILLER           1300 ACCOUNTING
      7566 JONES            2975 RESEARCH
      7902 FORD             3000 RESEARCH
      7876 ADAMS            1100 RESEARCH
      7369 SMITH            1000 RESEARCH
      7788 SCOTT            3000 RESEARCH
      7521 WARD             4000 SALES
      7844 TURNER           1500 SALES
      7499 ALLEN            2000 SALES
      7900 JAMES             950 SALES
      7698 BLAKE            2850 SALES
      7654 MARTIN           1250 SALES

14 rows selected.

Related Links:
- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle
- Create CSV file using PL/SQL
- Load CSV file in Oracle using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Generate XML Output from SQL