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


      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

          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

            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

              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.