This discussion is archived
7 Replies Latest reply: Aug 25, 2013 10:35 PM by Karthick_Arp RSS

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

980668 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated


    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

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