2 Replies Latest reply: Aug 23, 2013 8:00 AM by Srini Chavali-Oracle 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