7 Replies Latest reply: Aug 26, 2013 12:35 AM by Karthick_Arp RSS

    SQL loader to load XML file to two tables (parent and child) ?

    sagt.vishwa

      Hi Friends;

         I have following XML file.

       

      <ImportLineRelease  xmlns="example.oracle.com">

           <eDOSeqNo>1</eDOSeqNo>

           <MsgType>O</MsgType>

           <AgentDO>2013032317</AgentDO>

           <SerialNo>14</SerialNo>

           <BL>3551107860</BL>

           <DOExpiry>2013-10-25</DOExpiry>

           <Officer>John</Officer>

           <VesName>Dummy Vessel</VesName>

           <VesVoy>001</VesVoy>

           <VesRef>REF001</VesRef>

           <DC/>

           <Consignee>

                <ConsigneeTIN>NA</ConsigneeTIN>

                <ConsigneeName>Dummy</ConsigneeName>

                <ConsigneeAddress>Kolkata,India</ConsigneeAddress>

           </Consignee>

           <ContCount>

                <ContCount20>1</ContCount20>

                <ContCount40>0</ContCount40>

                <ContCountOver40>0</ContCountOver40>

           </ContCount>

           <Container>

                <Cont>

                     <ContNumber>C001</ContNumber>   

                     <ContMrkNo>M001</ContMrkNo>   

                     <ContPkgType>PK001</ContPkgType>   

                     <ContGoodsDescrip>Description 001</ContGoodsDescrip>   

                     <ContGrossWt>14000.000</ContGrossWt>

                     <ContCBM>24.000</ContCBM>   

                     <ContCIF>0.00</ContCIF>   

                </Cont>

                <Cont>

                     <ContNumber>C002</ContNumber>

                     <ContMrkNo>M002</ContMrkNo>

                     <ContPkgType>PK002</ContPkgType>

                     <ContGoodsDescrip>Description 002</ContGoodsDescrip>

                     <ContGrossWt>14000.000</ContGrossWt>

                     <ContCBM>24.000</ContCBM>

                     <ContCIF>0.00</ContCIF>

                </Cont>

           </Container>

      </ImportLineRelease>

       

      I have 2 tables with follwing structure

       

      desc delivery_order;

       

      eDOSeqNo varchar2 (20),

      MsgType varchar2 (20),

      AgentDO varchar2 (20),

      SerialNo varchar2 (20),

      BL varchar2 (20),

      DOExpiry varchar2 (20),

      Officer varchar2 (20),

      VesName varchar2 (20),

      VesVoy varchar2 (20),

      VesRef varchar2 (20),

      DC varchar2 (20),

      ConsigneeTIN varchar2 (20),

      ConsigneeName varchar2 (20),

      ConsigneeAddress varchar2 (20),

      ContCount20 varchar2 (20),

      ContCount40 varchar2 (20),

      ContCountOver40 varchar2 (20)

       

       

      desc desc delivery_container;

      eDOSeqNo varchar2 (20),     fk of delivery_order.eDOSeqNo

      ContNumber varchar2 (20),

      ContMrkNo varchar2 (20),

      ContPkgType varchar2 (20),

      ContGoodsDescrip varchar2 (20),

      ContGrossWt varchar2 (20),

      ContCBM varchar2 (20),

      ContCIF varchar2 (20)

       

       

      I need to load these xml file data to these two tables using SQLLoader or other good method. Hope best way is SQLLoader. Because these files are having large data content.

      Please help me how to do it using SQL Loader . ?

       

      After inserting data to tables looks like;

       

      select * from delivery_order;

       

      eDOSeqNoMsgTypeAgentDOSerialNoBLDOExpiryOfficerVesNameVesVoyVesRefDCConsigneeTINConsigneeNameConsigneeAddressContCount20ContCount40ContCountOver40

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      1O20130323171435511078602013-10-25JohnDummy Vessel001REF001NADummyKolkata,India100

       

      select * from delivery_container;

       

      eDOSeqNoContNumberContMrkNoContPkgTypeContGoodsDescripContGrossWtContCBMContCIF

      --------------------------------------------------------------------------------------------------------------------------------------------------------

      1C001M001PK001Description 00114000240
      1C002M002PK002Description 00214000240

       

      Regards

      Vishwa

        • 1. Re: SQL loader to load XML file to two tables (parent and child) ?
          Karthick_Arp


          Where is your xml file? If it is in your server you can just do like this.

           

          This is my sample 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>

           

          Now from oracle i just query this file like this.

           

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

           

               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

           

          If it is not in the server and you have it in the client then just load the file into a xmltype column and use the above query on the table.

          • 2. Re: SQL loader to load XML file to two tables (parent and child) ?
            AlbertoFaenza

            Hi,

             

            you can also check this example from Solomon using SQL*Loader: Re: Load XML File using SQL Loader

             

            Regards.

            Alberto

            • 3. Re: SQL loader to load XML file to two tables (parent and child) ?
              odie_63

              SQL*Loader is not an XML parser.

               

              As Karthick said, if the file resides client-side, you may use SQL*Loader to load it in a table, then process it from there in a second step.

              And the best way to parse the XML once it's inside the database depends on the exact version, which you didn't give.

              • 4. Re: SQL loader to load XML file to two tables (parent and child) ?
                odie_63

                Why has this thread been moved back here ?

                 

                OP's main problem won't be resolved using SQL*Loader.

                • 5. Re: SQL loader to load XML file to two tables (parent and child) ?
                  Barbara Boehmer

                  If the xml file is on the server, not the client, or you can somehow move it to the server, then I would use just SQL, as demonstrated below.

                   

                   

                  SCOTT@orcl12c> host type xmlfile.txt

                  <ImportLineRelease  xmlns="example.oracle.com">

                       <eDOSeqNo>1</eDOSeqNo>

                       <MsgType>O</MsgType>

                       <AgentDO>2013032317</AgentDO>

                       <SerialNo>14</SerialNo>

                       <BL>3551107860</BL>

                       <DOExpiry>2013-10-25</DOExpiry>

                       <Officer>John</Officer>

                       <VesName>Dummy Vessel</VesName>

                       <VesVoy>001</VesVoy>

                       <VesRef>REF001</VesRef>

                       <DC/>

                       <Consignee>

                            <ConsigneeTIN>NA</ConsigneeTIN>

                            <ConsigneeName>Dummy</ConsigneeName>

                            <ConsigneeAddress>Kolkata,India</ConsigneeAddress>

                       </Consignee>

                       <ContCount>

                            <ContCount20>1</ContCount20>

                            <ContCount40>0</ContCount40>

                            <ContCountOver40>0</ContCountOver40>

                       </ContCount>

                       <Container>

                            <Cont>

                                 <ContNumber>C001</ContNumber>

                                 <ContMrkNo>M001</ContMrkNo>

                                 <ContPkgType>PK001</ContPkgType>

                                 <ContGoodsDescrip>Description 001</ContGoodsDescrip>

                                 <ContGrossWt>14000.000</ContGrossWt>

                                 <ContCBM>24.000</ContCBM>

                                 <ContCIF>0.00</ContCIF>

                            </Cont>

                            <Cont>

                                 <ContNumber>C002</ContNumber>

                                 <ContMrkNo>M002</ContMrkNo>

                                 <ContPkgType>PK002</ContPkgType>

                                 <ContGoodsDescrip>Description 002</ContGoodsDescrip>

                                 <ContGrossWt>14000.000</ContGrossWt>

                                 <ContCBM>24.000</ContCBM>

                                 <ContCIF>0.00</ContCIF>

                            </Cont>

                       </Container>

                  </ImportLineRelease>

                   

                  SCOTT@orcl12c> CREATE TABLE delivery_order

                    2    (eDOSeqNo         varchar2 (20) PRIMARY KEY,

                    3     MsgType          varchar2 (20),

                    4     AgentDO          varchar2 (20),

                    5     SerialNo         varchar2 (20),

                    6     BL               varchar2 (20),

                    7     DOExpiry         varchar2 (20),

                    8     Officer          varchar2 (20),

                    9     VesName          varchar2 (20),

                  10     VesVoy           varchar2 (20),

                  11     VesRef           varchar2 (20),

                  12     DC               varchar2 (20),

                  13     ConsigneeTIN     varchar2 (20),

                  14     ConsigneeName    varchar2 (20),

                  15     ConsigneeAddress varchar2 (20),

                  16     ContCount20      varchar2 (20),

                  17     ContCount40      varchar2 (20),

                  18     ContCountOver40  varchar2 (20))

                  19  /

                   

                  Table created.

                   

                  SCOTT@orcl12c> CREATE TABLE delivery_container

                    2    (eDOSeqNo         varchar2 (20) REFERENCES delivery_order,

                    3     ContNumber       varchar2 (20),

                    4     ContMrkNo        varchar2 (20),

                    5     ContPkgType      varchar2 (20),

                    6     ContGoodsDescrip varchar2 (20),

                    7     ContGrossWt      varchar2 (20),

                    8     ContCBM          varchar2 (20),

                    9     ContCIF          varchar2 (20))

                  10  /

                   

                  Table created.

                   

                  SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'

                    2  /

                   

                  Directory created.

                   

                  SCOTT@orcl12c> INSERT INTO delivery_order

                    2  SELECT t.*

                    3  FROM   (SELECT XMLTYPE (BFILENAME ('MY_DIR', 'xmlfile.txt'), NLS_CHARSET_ID ('AL32UTF8')) xml_data

                    4          FROM   DUAL) x,

                    5         XMLTABLE

                    6           (XMLNAMESPACES (DEFAULT 'example.oracle.com'),

                    7            'ImportLineRelease'

                    8            PASSING x.xml_data

                    9            COLUMNS

                  10              eDOSeqNo         varchar2 (20) PATH 'eDOSeqNo',

                  11              MsgType          varchar2 (20) PATH 'MsgType',

                  12              AgentDO          varchar2 (20) PATH 'AgentDO',

                  13              SerialNo         varchar2 (20) PATH 'SerialNo',

                  14              BL               varchar2 (20) PATH 'BL',

                  15              DOExpiry         varchar2 (20) PATH 'DOExpiry',

                  16              Officer          varchar2 (20) PATH 'Officer',

                  17              VesName          varchar2 (20) PATH 'VesName',

                  18              VesVoy           varchar2 (20) PATH 'VesVoy',

                  19              VesRef           varchar2 (20) PATH 'VesRef',

                  20              DC               varchar2 (20) PATH 'DC',

                  21              ConsigneeTIN     varchar2 (20) PATH 'Consignee/ConsigneeTIN',

                  22              ConsigneeName    varchar2 (20) PATH 'Consignee/ConsigneeName',

                  23              ConsigneeAddress varchar2 (20) PATH 'Consignee/ConsigneeAddress',

                  24              ContCount20      varchar2 (20) PATH 'ContCount/ContCount20',

                  25              ContCount40      varchar2 (20) PATH 'ContCount/ContCount40',

                  26              ContCountOver40  varchar2 (20) PATH 'ContCount/ContCountOver40') t

                  27  /

                   

                  1 row created.

                   

                  SCOTT@orcl12c> INSERT INTO delivery_container

                    2  SELECT t.eDOSeqNo, t2.*

                    3  FROM   (SELECT XMLTYPE (BFILENAME ('MY_DIR', 'xmlfile.txt'), NLS_CHARSET_ID ('AL32UTF8')) xml_data

                    4          FROM   DUAL) x,

                    5         XMLTABLE

                    6           (XMLNAMESPACES (DEFAULT 'example.oracle.com'),

                    7            'ImportLineRelease'

                    8            PASSING x.xml_data

                    9            COLUMNS

                  10              eDOSeqNo         varchar2 (20) PATH 'eDOSeqNo',

                  11              COLUMN_VALUE     XMLTYPE       PATH 'Container/Cont') t,

                  12         XMLTABLE

                  13           (XMLNAMESPACES (DEFAULT 'example.oracle.com'),

                  14            'Cont'

                  15            PASSING t.COLUMN_VALUE

                  16            COLUMNS

                  17              ContNumber       varchar2 (20) PATH 'ContNumber',

                  18              ContMrkNo        varchar2 (20) PATH 'ContMrkNo',

                  19              ContPkgType      varchar2 (20) PATH 'ContPkgType',

                  20              ContGoodsDescrip varchar2 (20) PATH 'ContGoodsDescrip',

                  21              ContGrossWt      varchar2 (20) PATH 'ContGrossWt',

                  22              ContCBM          varchar2 (20) PATH 'ContCBM',

                  23              ContCIF          varchar2 (20) PATH 'ContCIF') t2

                  24  /

                   

                  2 rows created.

                   

                  SCOTT@orcl12c> SELECT * FROM delivery_order

                    2  /

                   

                  EDOSEQNO             MSGTYPE              AGENTDO              SERIALNO             BL                DOEXPIRY        OFFICER

                     VESNAME              VESVOY               VESREF               DC                   CONSIGNEETIN     CONSIGNEENAME    CONSIGNEE

                  ADDRESS     CONTCOUNT20    CONTCOUNT40          CONTCOUNTOVER40

                  -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----

                  ---------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------

                  ------------ -------------------- -------------------- -------------------- --------------------

                  1                    O                    2013032317           14                   3551107860        2013-10-25              John

                                     Dummy Vessel         001                  REF001                                    NA                   Dummy

                           Kolkata,India        1                    0                    0

                   

                  1 row selected.

                   

                  SCOTT@orcl12c> SELECT * FROM delivery_container

                    2  /

                   

                  EDOSEQNO             CONTNUMBER           CONTMRKNO            CONTPKGTYPE          CONTGOODSDESCRIP  CONTGROSSWT             CONT

                  CBM                CONTCIF

                  -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----

                  ---------------- --------------------

                  1                    C001                 M001                 PK001                Description 001   14000.000       24.000

                     0.00

                  1                    C002                 M002                 PK002                Description 002   14000.000       24.000

                     0.00

                   

                  2 rows selected.

                  • 6. Re: SQL loader to load XML file to two tables (parent and child) ?
                    Barbara Boehmer

                    If the file is on the client and you have no other way to get it to the server than SQL*Loader, then I would load it into a staging table with one xmltype column, then use SQL to insert it from there, as demonstrated below.  Although you could load the data into one of the tables directly, the other would require use of an object or varray.  There would also be the problem of what if you have <tag/> instead of <tag> and </tag>.  It could be done with SQL*Loader alone, but it would be messy.

                     

                     

                     

                    SCOTT@orcl12c> host type xmlfile.txt

                    <ImportLineRelease  xmlns="example.oracle.com">

                         <eDOSeqNo>1</eDOSeqNo>

                         <MsgType>O</MsgType>

                         <AgentDO>2013032317</AgentDO>

                         <SerialNo>14</SerialNo>

                         <BL>3551107860</BL>

                         <DOExpiry>2013-10-25</DOExpiry>

                         <Officer>John</Officer>

                         <VesName>Dummy Vessel</VesName>

                         <VesVoy>001</VesVoy>

                         <VesRef>REF001</VesRef>

                         <DC/>

                         <Consignee>

                              <ConsigneeTIN>NA</ConsigneeTIN>

                              <ConsigneeName>Dummy</ConsigneeName>

                              <ConsigneeAddress>Kolkata,India</ConsigneeAddress>

                         </Consignee>

                         <ContCount>

                              <ContCount20>1</ContCount20>

                              <ContCount40>0</ContCount40>

                              <ContCountOver40>0</ContCountOver40>

                         </ContCount>

                         <Container>

                              <Cont>

                                   <ContNumber>C001</ContNumber>

                                   <ContMrkNo>M001</ContMrkNo>

                                   <ContPkgType>PK001</ContPkgType>

                                   <ContGoodsDescrip>Description 001</ContGoodsDescrip>

                                   <ContGrossWt>14000.000</ContGrossWt>

                                   <ContCBM>24.000</ContCBM>

                                   <ContCIF>0.00</ContCIF>

                              </Cont>

                              <Cont>

                                   <ContNumber>C002</ContNumber>

                                   <ContMrkNo>M002</ContMrkNo>

                                   <ContPkgType>PK002</ContPkgType>

                                   <ContGoodsDescrip>Description 002</ContGoodsDescrip>

                                   <ContGrossWt>14000.000</ContGrossWt>

                                   <ContCBM>24.000</ContCBM>

                                   <ContCIF>0.00</ContCIF>

                              </Cont>

                         </Container>

                    </ImportLineRelease>

                     

                    SCOTT@orcl12c> host type test.ctl

                    LOAD DATA

                    INFILE xmlfile.txt

                    CONTINUEIF THIS PRESERVE (1:20) != '</ImportLineRelease>'

                    INTO TABLE staging

                    WHEN (1:18) = '<ImportLineRelease'

                    FIELDS

                    (xml_data CHAR(5000))

                     

                    SCOTT@orcl12c> CREATE TABLE delivery_order

                      2    (eDOSeqNo         varchar2 (20) PRIMARY KEY,

                      3     MsgType          varchar2 (20),

                      4     AgentDO          varchar2 (20),

                      5     SerialNo         varchar2 (20),

                      6     BL               varchar2 (20),

                      7     DOExpiry         varchar2 (20),

                      8     Officer          varchar2 (20),

                      9     VesName          varchar2 (20),

                    10     VesVoy           varchar2 (20),

                    11     VesRef           varchar2 (20),

                    12     DC               varchar2 (20),

                    13     ConsigneeTIN     varchar2 (20),

                    14     ConsigneeName    varchar2 (20),

                    15     ConsigneeAddress varchar2 (20),

                    16     ContCount20      varchar2 (20),

                    17     ContCount40      varchar2 (20),

                    18     ContCountOver40  varchar2 (20))

                    19  /

                     

                    Table created.

                     

                    SCOTT@orcl12c> CREATE TABLE delivery_container

                      2    (eDOSeqNo         varchar2 (20) REFERENCES delivery_order,

                      3     ContNumber       varchar2 (20),

                      4     ContMrkNo        varchar2 (20),

                      5     ContPkgType      varchar2 (20),

                      6     ContGoodsDescrip varchar2 (20),

                      7     ContGrossWt      varchar2 (20),

                      8     ContCBM          varchar2 (20),

                      9     ContCIF          varchar2 (20))

                    10  /

                     

                    Table created.

                     

                    SCOTT@orcl12c> CREATE TABLE staging

                      2    (xml_data         XMLTYPE)

                      3  /

                     

                    Table created.

                     

                    SCOTT@orcl12c> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

                     

                    SQL*Loader: Release 12.1.0.1.0 - Production on Fri Aug 23 14:09:21 2013

                     

                    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

                     

                    Path used:      Conventional

                    Commit point reached - logical record count 1

                     

                    Table STAGING:

                      1 Row successfully loaded.

                     

                    Check the log file:

                      test.log

                    for more information about the load.

                     

                    SCOTT@orcl12c> SELECT * FROM staging

                      2  /

                     

                    XML_DATA

                    ----------------------------------------------------------------------------------------------------------------------------------

                    ----------------------------------------------------------------------------------------------------------------------------------

                    ----------------------------------------------------------------------------------------------------------------------------------

                    ----------

                    <ImportLineRelease xmlns="example.oracle.com">

                      <eDOSeqNo>1</eDOSeqNo>

                      <MsgType>O</MsgType>

                      <AgentDO>2013032317</AgentDO>

                      <SerialNo>14</SerialNo>

                      <BL>3551107860</BL>

                      <DOExpiry>2013-10-25</DOExpiry>

                      <Officer>John</Officer>

                      <VesName>Dummy Vessel</VesName>

                      <VesVoy>001</VesVoy>

                      <VesRef>REF001</VesRef>

                      <DC/>

                      <Consignee>

                        <ConsigneeTIN>NA</ConsigneeTIN>

                        <ConsigneeName>Dummy</ConsigneeName>

                        <ConsigneeAddress>Kolkata,India</ConsigneeAddress>

                      </Consignee>

                      <ContCount>

                        <ContCount20>1</ContCount20>

                        <ContCount40>0</ContCount40>

                        <ContCountOver40>0</ContCountOver40>

                      </ContCount>

                      <Container>

                        <Cont>

                          <ContNumber>C001</ContNumber>

                          <ContMrkNo>M001</ContMrkNo>

                          <ContPkgType>PK001</ContPkgType>

                          <ContGoodsDescrip>Description 001</ContGoodsDescrip>

                          <ContGrossWt>14000.000</ContGrossWt>

                          <ContCBM>24.000</ContCBM>

                          <ContCIF>0.00</ContCIF>

                        </Cont>

                        <Cont>

                          <ContNumber>C002</ContNumber>

                          <ContMrkNo>M002</ContMrkNo>

                          <ContPkgType>PK002</ContPkgType>

                          <ContGoodsDescrip>Description 002</ContGoodsDescrip>

                          <ContGrossWt>14000.000</ContGrossWt>

                          <ContCBM>24.000</ContCBM>

                          <ContCIF>0.00</ContCIF>

                        </Cont>

                      </Container>

                    </ImportLineRelease>

                     

                     

                    1 row selected.

                     

                    SCOTT@orcl12c> INSERT INTO delivery_order

                      2  SELECT t.*

                      3  FROM   staging x,

                      4         XMLTABLE

                      5           (XMLNAMESPACES (DEFAULT 'example.oracle.com'),

                      6            'ImportLineRelease'

                      7            PASSING x.xml_data

                      8            COLUMNS

                      9              eDOSeqNo         varchar2 (20) PATH 'eDOSeqNo',

                    10              MsgType          varchar2 (20) PATH 'MsgType',

                    11              AgentDO          varchar2 (20) PATH 'AgentDO',

                    12              SerialNo         varchar2 (20) PATH 'SerialNo',

                    13              BL               varchar2 (20) PATH 'BL',

                    14              DOExpiry         varchar2 (20) PATH 'DOExpiry',

                    15              Officer          varchar2 (20) PATH 'Officer',

                    16              VesName          varchar2 (20) PATH 'VesName',

                    17              VesVoy           varchar2 (20) PATH 'VesVoy',

                    18              VesRef           varchar2 (20) PATH 'VesRef',

                    19              DC               varchar2 (20) PATH 'DC',

                    20              ConsigneeTIN     varchar2 (20) PATH 'Consignee/ConsigneeTIN',

                    21              ConsigneeName    varchar2 (20) PATH 'Consignee/ConsigneeName',

                    22              ConsigneeAddress varchar2 (20) PATH 'Consignee/ConsigneeAddress',

                    23              ContCount20      varchar2 (20) PATH 'ContCount/ContCount20',

                    24              ContCount40      varchar2 (20) PATH 'ContCount/ContCount40',

                    25              ContCountOver40  varchar2 (20) PATH 'ContCount/ContCountOver40') t

                    26  /

                     

                    1 row created.

                     

                    SCOTT@orcl12c> INSERT INTO delivery_container

                      2  SELECT t.eDOSeqNo, t2.*

                      3  FROM   staging x,

                      4         XMLTABLE

                      5           (XMLNAMESPACES (DEFAULT 'example.oracle.com'),

                      6            'ImportLineRelease'

                      7            PASSING x.xml_data

                      8            COLUMNS

                      9              eDOSeqNo         varchar2 (20) PATH 'eDOSeqNo',

                    10              COLUMN_VALUE     XMLTYPE       PATH 'Container/Cont') t,

                    11         XMLTABLE

                    12           (XMLNAMESPACES (DEFAULT 'example.oracle.com'),

                    13            'Cont'

                    14            PASSING t.COLUMN_VALUE

                    15            COLUMNS

                    16              ContNumber       varchar2 (20) PATH 'ContNumber',

                    17              ContMrkNo        varchar2 (20) PATH 'ContMrkNo',

                    18              ContPkgType      varchar2 (20) PATH 'ContPkgType',

                    19              ContGoodsDescrip varchar2 (20) PATH 'ContGoodsDescrip',

                    20              ContGrossWt      varchar2 (20) PATH 'ContGrossWt',

                    21              ContCBM          varchar2 (20) PATH 'ContCBM',

                    22              ContCIF          varchar2 (20) PATH 'ContCIF') t2

                    23  /

                     

                    2 rows created.

                     

                    SCOTT@orcl12c> SELECT * FROM delivery_order

                      2  /

                     

                    EDOSEQNO             MSGTYPE              AGENTDO              SERIALNO             BL                DOEXPIRY        OFFICER

                       VESNAME              VESVOY               VESREF               DC                   CONSIGNEETIN     CONSIGNEENAME    CONSIGNEE

                    ADDRESS     CONTCOUNT20    CONTCOUNT40          CONTCOUNTOVER40

                    -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----

                    ---------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------

                    ------------ -------------------- -------------------- -------------------- --------------------

                    1                    O                    2013032317           14                   3551107860        2013-10-25              John

                                       Dummy Vessel         001                  REF001                                    NA                   Dummy

                             Kolkata,India        1                    0                    0

                     

                    1 row selected.

                     

                    SCOTT@orcl12c> SELECT * FROM delivery_container

                      2  /

                     

                    EDOSEQNO             CONTNUMBER           CONTMRKNO            CONTPKGTYPE          CONTGOODSDESCRIP  CONTGROSSWT             CONT

                    CBM                CONTCIF

                    -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----

                    ---------------- --------------------

                    1                    C001                 M001                 PK001                Description 001   14000.000       24.000

                       0.00

                    1                    C002                 M002                 PK002                Description 002   14000.000       24.000

                       0.00

                     

                    2 rows selected.

                    • 7. Re: SQL loader to load XML file to two tables (parent and child) ?
                      Karthick_Arp

                      This is not the first time its happening.. I did enquired about it.. But not much response.

                       

                      Request: PL/SQL, External Table and SQL Loader