This discussion is archived
3 Replies Latest reply: Jul 18, 2013 4:30 AM by odie_63 RSS

How to load a XML file into a table using PL/SQL

ODI_NewUser Newbie
Currently Being Moderated


Hi Guru,

I have a requirement, that i have to create a procedure or a package in PL/SQL to load  XML file into a table.

How we can achive this.

  • 1. Re: How to load a XML file into a table using PL/SQL
    Hema Newbie
    Currently Being Moderated

    What is your exact requirement? You mean, you have to insert the XML elements into a table?

  • 2. Re: How to load a XML file into a table using PL/SQL
    Karthick_Arp Guru
    Currently Being Moderated

    ODI_NewUser wrote:

     


    Hi Guru,

    I have a requirement, that i have to create a procedure or a package in PL/SQL to load  XML file into a table.

    How we can achive this.

     

    Not a perfectly framed question. How do you want to load the XML file? Hoping you want to parse the xml file and load it into a table you can do this.

     

    This is the xml file

     

    karthick% cat emp_details.xml

    <?xml version="1.0"?>

    <ROWSET>

    <ROW>

      <EMPNO>7782</EMPNO>

      <ENAME>CLARK</ENAME>

      <JOB>MANAGER</JOB>

      <MGR>7839</MGR>

      <HIREDATE>09-JUN-1981</HIREDATE>

      <SAL>2450</SAL>

      <COM>0</COM>

      <DEPTNO>10</DEPTNO>

    </ROW>

    <ROW>

      <EMPNO>7839</EMPNO>

      <ENAME>KING</ENAME>

      <JOB>PRESIDENT</JOB>

      <HIREDATE>17-NOV-1981</HIREDATE>

      <SAL>5000</SAL>

      <COM>0</COM>

      <DEPTNO>10</DEPTNO>

    </ROW>

    </ROWSET>

     

    You can write a query like this.

     

    SQL> select *
      2    from xmltable
      3         (
      4            '/ROWSET/ROW'  passing xmltype
      5            (
      6                 bfilename('SDAARBORDIRLOG', 'emp_details.xml')
      7               , nls_charset_id('AL32UTF8')
      8            )
      9            columns empno    number      path 'EMPNO'
    10                  , ename    varchar2(6) path 'ENAME'
    11                  , job      varchar2(9) path 'JOB'
    12                  , mgr      number      path 'MGR'
    13                  , hiredate varchar2(20)path 'HIREDATE'
    14                  , sal      number      path 'SAL'
    15                  , com      number      path 'COM'
    16                  , deptno   number      path 'DEPTNO'
    17         );

         EMPNO ENAME  JOB              MGR HIREDATE                    SAL        COM     DEPTNO
    ---------- ------ --------- ---------- -------------------- ---------- ---------- ----------
          7782 CLARK  MANAGER         7839 09-JUN-1981                2450          0         10
          7839 KING   PRESIDENT            17-NOV-1981                5000          0         10

     

    SQL>

  • 3. Re: How to load a XML file into a table using PL/SQL
    odie_63 Guru
    Currently Being Moderated

    I have a requirement, that i have to create a procedure or a package in PL/SQL to load  XML file into a table.

    How we can achive this.

    That's a very vague requirement.

    The more details you give us the more we'll be able to provide a relevant solution.

     

    - What's the db version?

    - Where's the XML located (file, variable, column) ?

    - What is (are) the structure(s) of the target table(s) ?

    - Which element/attribute should go to which column ?

     

    Karthick showed a first approach that is OK for relatively small input documents.

    Your real scenario may be different, and there are other methods to improve performance (assuming it's one of your concern).

     

    I suggest you post again in the XML DB forum, while including as much details as you can.

    Have a read on the FAQ there too.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points