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

22 comments:

  1. 1.I want to know , how do you load if a xml tag has child records and multiple branches
    2.If xml tag is repeating and i mean if values are repeating , then how do u loop it and insert

    ReplyDelete
    Replies
    1. you do not need a loop for repeating values, here we have imported 14 records from xml file by this..

      Delete
    2. Q1. that i also have to figure out :) but you can always get xmldb help from http://odieweblog.wordpress.com, one of the best xml blog i know..

      Delete
  2. Dear Sir , i face a problem to uplaod a xml data to load a oracle table.pls.help me urgent. i am using oracle 9.2.0.1.0 .
    my steps-
    SQL> create or replace directory myxml as 'D:\myxml\';
    Directory created.

    SQL> grant read, write on directory myxml to do;
    Grant succeeded.
    create table info
    (cus_off_code varchar2(5),
    voy_number varchar2(5),
    Date_Depature date
    )

    my xml file in location




    301
    020W
    07/12/2013
    -----------------------
    following errors i get
    SQL> INSERT INTO info (CUS_OFF_CODE, VOY_NUMBER, DATE_DEPATURE)
    2 SELECT *
    3 FROM XMLTABLE('/General_segment/General_segment_id'PASSING XMLTYPE(BFILENAME('myxml', 'IMPORT.XML'),NLS_CHARSET_ID('UTF-8'))
    4 COLUMNS Custom_office_code VARCHAR2(5) PATH 'CUS_OFF_CODE',
    5 Voyage_number VARCHAR2(5) PATH 'VOY_NUMBER',
    6 Date_of_departure DATE PATH 'DATE_DEPATURE'
    7 ) ;
    FROM XMLTABLE('/General_segment/General_segment_id'PASSING XMLTYPE(BFILENAME('myxml', 'IMPORT.XML'),NLS_CHARSET_ID('UTF-8'))
    *
    ERROR at line 3:
    ORA-00933: SQL command not properly ended

    ReplyDelete
    Replies
    1. Oracle 9i is depreciated a long back. Please switch to at-least 11g R2 to get the things moving.

      Delete
  3. Thank you sir,
    but at this moment we can't switch 11g. Have any other options?
    your reply in this regard highly appreciated.
    thanks
    partha

    ReplyDelete
    Replies
    1. http://www.stanford.edu/dept/itss/docs/oracle/9i/appdev.920/a96620/toc.htm

      Delete
  4. hi Mr
    I use Devloper9i AS, oracle 9i, Winxp

    I have a (data.txt) text file and data.htm (html file) as well which contains climate data for example

    OLBA 25/23....

    which means temperature is 25 and humidity 23

    I already created a table climate table having two fields: temperature and humidity filed



    My question I need a PLSQL procedure or function which open these two file(data.txt,data.html) , read their contain(temperature , humidity ), to finally insert them into my table climate.



    Thank You for any support.

    ReplyDelete
    Replies
    1. this may be helpful to you
      http://nimishgarg.blogspot.in/2013/04/load-csv-file-in-oracle-using-plsql.html

      Delete
  5. if my xml file has some tags without data like (only with closing tag)..how to handle this cases??

    ReplyDelete
    Replies
    1. It will have null values in column then.. or you can simply avoid them in XMLTABLE COLUMNS part.

      Delete
  6. Hi! thank u for post. Could u share your xml exmaple EMP_DEPT.XML?

    ReplyDelete
    Replies
    1. Use following query to generate XML for this

      SELECT
      DBMS_XMLGEN.GETXML('
      SELECT
      EMPNO, ENAME, SAL, DNAME
      FROM
      SCOTT.EMP E,
      SCOTT.DEPT D
      WHERE
      D.DEPTNO=E.DEPTNO
      ')
      FROM DUAL;

      Delete
  7. This is not working if we want to load 2 GB of data.
    ORA-04030: out of process memory when trying to allocate 1032 bytes (qmxlu subheap,qmemNextBuf:alloc)
    ORA-06512: at line 9
    please help

    ReplyDelete
  8. Suppose I want to create a trigger for if any changes occur in table x then I will write a XML for taking all the columns and will store that XML into another table y in trigger

    ReplyDelete
    Replies
    1. you can use query used in this post to get xml values of table
      http://nimishgarg.blogspot.in/2013/04/create-xml-file-using-plsql.html

      Delete
  9. Could you please show the XML file content?

    ReplyDelete
    Replies
    1. I used same file which I generated by http://nimishgarg.blogspot.in/2013/04/create-xml-file-using-plsql.html

      Delete
  10. will this work on windows ? we must give path file ?

    ReplyDelete
  11. Excellent postings from you nimish great work

    ReplyDelete
  12. hi

    if any mistake is in any xml tag, that row only should not process. rest of the rows should process into table. how to do this.

    example:

    suppose in xml for

    7782 CLARK 2450 ACCOUNTING the tag has not closed like <CLARK.

    how to process the bad record in xml table .

    ReplyDelete
  13. Hello every one, here every person is sharing these familiarity,
    therefore it's nice to read this blog, and I used to visit this blog every day.

    ReplyDelete