1 2 Previous Next 17 Replies Latest reply: Jul 4, 2013 5:07 AM by 957539 RSS

    Need Help to migrate data from XML to oracle table.

    957539

      Hi Odie,

       

      Im trying to insert data from xml into a table.

       

      My XML file is as follows.

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

       

      - <TransferXML>

       

       

      <BatchName>HJ69240</BatchName>

       

       

      <BatchStatus>park</BatchStatus>

       

       

      - <SubBatch>

       

       

      <SubBatchName>UQ80288</SubBatchName>

       

       

      <SubBatchStatus>draft</SubBatchStatus>

       

       

      - <SubBatchDetail>

       

       

      <Ingredient>I3308</Ingredient>

       

       

      <DispensedQty>0.0</DispensedQty>

       

       

      <MDispensedQty>4.0</MDispensedQty>

       

      </SubBatchDetail>

       

      - <SubBatchDetail>

       

       

      <Ingredient>I3261</Ingredient>

       

       

      <DispensedQty>0.0</DispensedQty>

       

       

      <MDispensedQty>3.5</MDispensedQty>

       

      </SubBatchDetail>

       

      - <SubBatchDetail>

       

       

      <Ingredient>I3235</Ingredient>

       

       

      <DispensedQty>0.0</DispensedQty>

       

       

      <MDispensedQty>0.5</MDispensedQty>

       

      </SubBatchDetail>

       

      - <SubBatchDetail>

       

       

      <Ingredient>I3142</Ingredient>

       

       

      <DispensedQty>0.0</DispensedQty>

       

       

      <MDispensedQty>0.2</MDispensedQty>

       

      </SubBatchDetail>

      </SubBatch>

       

      - <SubBatch>

       

       

      <SubBatchName>ZB97913</SubBatchName>

       

       

      <SubBatchStatus>Incomplete</SubBatchStatus>

       

       

      - <SubBatchDetail>

       

       

      <Ingredient>I3309</Ingredient>

       

       

      <DispensedQty>0.75</DispensedQty>

       

       

      <MDispensedQty>0.0</MDispensedQty>

       

      </SubBatchDetail>

       

      - <SubBatchDetail>

       

       

      <Ingredient>I3436</Ingredient>

       

       

      <DispensedQty>0.0</DispensedQty>

       

       

      <MDispensedQty>0.05</MDispensedQty>

       

      </SubBatchDetail>

      </SubBatch>

       

      <Final>false</Final>

       

      </TransferXML>



      Using your previous posts i have migrated data from this xml to a table which is working perfectly fine.But this data is repeating instead of giving 6 lines its returning 12 lines please guide.


       

       

      CREATE TABLE XXBATCH AS

      SELECT A.BatchName ,A.BatchStatus,B.SubBatchName,B.SubBatchStatus,C.Ingredient , C.DispensedQty , C.MDispensedQty ,A.FINAL

      FROM ( XMLTable('/TransferXML'

              passing xmltype(

                       bfilename('APPS_DATA_FILE_DIR','HJ69240.xml')

                      , nls_charset_id('AL32UTF8')

                      ) COLUMNS

                      BatchName VARCHAR2(99) path 'BatchName'

                    , BatchStatus VARCHAR2(999) path 'BatchStatus'

                    , Final VARCHAR2(99) path 'Final'))A ,

                    ( XMLTable('/TransferXML/SubBatch'

              passing xmltype(

                       bfilename('APPS_DATA_FILE_DIR','HJ69240.xml')

                      , nls_charset_id('AL32UTF8')

                      ) COLUMNS

                     SubBatchName VARCHAR2(99) path 'SubBatch/SubBatchName'

                    , SubBatchStatus VARCHAR2(99) path 'SubBatch/SubBatchStatus'

                    ))B,

                     ( XMLTable('/TransferXML/SubBatch/SubBatchDetail'

              passing xmltype(

                       bfilename('APPS_DATA_FILE_DIR','HJ69240.xml')

                      , nls_charset_id('AL32UTF8')

                      ) COLUMNS

                     Ingredient VARCHAR2(99) path 'SubBatchDetail/Ingredient'

                    , DispensedQty VARCHAR2(99) path 'SubBatchDetail/DispensedQty'

                    , MDispensedQty VARCHAR2(99) path 'SubBatchDetail/MDispensedQty'

                    ))C

        • 1. Re: Need Help to migrate data from XML to oracle table.
          odie_63

          I didn't test your query but apparently that's because you're accessing the same file three times instead of passing correlated nested groups from one XMLTable to the next.

           

          This should work better :

          SELECT A.BatchName

              , A.BatchStatus

              , B.SubBatchName

              , B.SubBatchStatus

              , C.Ingredient

              , C.DispensedQty

              , C.MDispensedQty

              , A.FINAL

          FROM XMLTable('/TransferXML'

                PASSING xmltype(bfilename('TEST_DIR','HJ69240.xml'), nls_charset_id('AL32UTF8'))

                COLUMNS BatchName    VARCHAR2(99)  path 'BatchName'

                      , BatchStatus  VARCHAR2(999) path 'BatchStatus'

                      , Final        VARCHAR2(99)  path 'Final'

                      , SubBatchList XMLTYPE      path 'SubBatch'

              ) A

            , XMLTable('/SubBatch'

                PASSING SubBatchList

                COLUMNS SubBatchName      VARCHAR2(99) path 'SubBatchName'

                      , SubBatchStatus    VARCHAR2(99) path 'SubBatchStatus'

                      , SubBatchDetailList XMLTYPE      path 'SubBatchDetail'

              ) B

            , XMLTable('/SubBatchDetail'

                PASSING SubBatchDetailList

                COLUMNS Ingredient    VARCHAR2(99) path 'Ingredient'

                      , DispensedQty  VARCHAR2(99) path 'DispensedQty'

                      , MDispensedQty VARCHAR2(99) path 'MDispensedQty'

              ) C

          ;

           

          Message was edited by: odie_63

          • 2. Re: Need Help to migrate data from XML to oracle table.
            957539

            Thank you so much ODIE you are a life saver.

             

            It worked.

             

            Thanks....& Regards

            • 3. Re: Need Help to migrate data from XML to oracle table.
              957539

              Hi Odie,

               

              Just wanted to know that what if i want some more columns to be added to that custom table.

               

              Then how can i do it.

               

              I'm using this code as suggested by you.

               

              insert into  XXOPM_INB_TAB

              SELECT A.BatchName

                  , A.BatchStatus

                  , B.SubBatchName

                  , B.SubBatchStatus

                  , C.Ingredient

                  , C.DispensedQty

                  , C.MDispensedQty

                  , A.FINAL

              FROM XMLTable('/TransferXML'

                    PASSING xmltype(bfilename('XX_AARAV','HJ69240.xml'), nls_charset_id('AL32UTF8'))

                    COLUMNS BatchName    VARCHAR2(99)  path 'BatchName'

                          , BatchStatus  VARCHAR2(999) path 'BatchStatus'

                          , Final        VARCHAR2(99)  path 'Final'

                          , SubBatchList XMLTYPE      path 'SubBatch'

                  ) A

                , XMLTable('/SubBatch'

                    PASSING SubBatchList

                    COLUMNS SubBatchName      VARCHAR2(99) path 'SubBatchName'

                          , SubBatchStatus    VARCHAR2(99) path 'SubBatchStatus'

                          , SubBatchDetailList XMLTYPE      path 'SubBatchDetail'

                  ) B

                , XMLTable('/SubBatchDetail'

                    PASSING SubBatchDetailList

                    COLUMNS Ingredient    VARCHAR2(99) path 'Ingredient'

                          , DispensedQty  VARCHAR2(99) path 'DispensedQty'

                          , MDispensedQty VARCHAR2(99) path 'MDispensedQty'

                  ) C;

               

               

              And if i use the table structure as

               

              CREATE TABLE APPS.XXOPM_INB_MAIN_TAB

              (

                BATCHNAME       VARCHAR2(1000 BYTE),

                BATCHSTATUS     VARCHAR2(1000 BYTE),

                SUBBATCHNAME    VARCHAR2(1000 BYTE),

                SUBBATCHSTATUS  VARCHAR2(1000 BYTE),

                INGREDIENT      VARCHAR2(1000 BYTE),

                DISPENSEDQTY    VARCHAR2(1000 BYTE),

                MDISPENSEDQTY   VARCHAR2(1000 BYTE),

                FINAL           VARCHAR2(1000 BYTE),

                INSERT_FLAG     VARCHAR2(1000 BYTE),

                UPDATE_FLAG     VARCHAR2(1000 BYTE),

                UPDATE_MESSAGE  VARCHAR2(1000 BYTE)

              )

               

              which has 3 extra columns INSERT_FLAG,UPDATE_FLAG,UPDATE_MESSAGE

               

              while inserting im getting some error.

               

              Please suggest how can i pass NULL value to these three columns.

               

              Regards

              Karim.

              • 4. Re: Need Help to migrate data from XML to oracle table.
                odie_63

                Please suggest how can i pass NULL value to these three columns.

                That's basic SQL (and good practice) : always give the column list in the INSERT statement.

                 

                insert into XXOPM_INB_TAB

                (

                  BATCHNAME

                , BATCHSTATUS

                , SUBBATCHNAME

                , SUBBATCHSTATUS

                , INGREDIENT

                , DISPENSEDQTY

                , MDISPENSEDQTY

                , FINAL

                )

                SELECT ...

                • 5. Re: Need Help to migrate data from XML to oracle table.
                  957539

                  It Works, i was actually passing the column name instead of null because of which it was giving me error.

                   

                  That's great thank you so much for your support Odie.

                   

                  Regards

                  Karim

                  • 6. Re: Need Help to migrate data from XML to oracle table.
                    957539

                    Hi Odie,

                     

                    Greetings for the day!!!!!!!!!!!!

                     

                    In the above example im trying to read a file which is in XML format.

                     

                    Here i have hard coded the file name but im just wondering in case i pass multiple files where the file names are different for each file then how can i pass the file name for the above code.

                     

                    Is there any way i can trace it using time stamp or sysdate or some other solution.

                     

                    Please suggest..

                     

                    Thanks in advance

                    Karim.

                    • 7. Re: Need Help to migrate data from XML to oracle table.
                      Jason_(A_Non)

                      Did you look into using the filename as a variable, such as

                      bfilename(l_directory,l_filename)
                      
                      • 8. Re: Need Help to migrate data from XML to oracle table.
                        957539

                        Hi Jason,

                         

                        Greetings for the day!!!!!!!

                         

                        Yes im doing the same way using a variable

                        1. bfilename(l_directory,l_filename) 

                        where i can hard code the directory name but the file name is changing always.

                        This file is always in XML format, so by chance is there any way where it could pick the correct file which is generated on that day.

                        And this file changes every time it generates.

                        Like sometime it is 'HJ69240.xml' or 'AB65868.xml'

                         

                        Please help.....

                         

                        Thanks in advance

                        Karim.

                        • 9. Re: Need Help to migrate data from XML to oracle table.
                          odie_63
                          but the file name is changing always

                          And you have no way to know what the current name is ?

                           

                          There are a few ways to handle this situation :

                           

                          - listing the content of the directory through an external table and the preprocessor directive

                          - listing the content of the directory via a Java stored function, exposed as a PL/SQL table function

                          • 10. Re: Need Help to migrate data from XML to oracle table.
                            957539

                            Hi Odie,

                             

                            Yes there is no way that i can guess the file because its generated from a machine which is used to generate OPM formula's.

                             

                            And all the files generated from this machine are unique.

                             

                            Can you share the code where i can use any one of the above.

                             

                            - listing the content of the directory through an external table and the preprocessor directive

                            - listing the content of the directory via a Java stored function, exposed as a PL/SQL table function

                             

                            I tried many functions but at OS level its not working.

                             

                            Please guide!!!!!!!!!

                             

                            Regards

                            Karim.

                            • 11. Re: Need Help to migrate data from XML to oracle table.
                              odie_63

                              Example for the external table approach :

                              http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html

                               

                              You'll find Java examples on the Internet by searching for keywords like "how to list a directory from Oracle".

                              • 12. Re: Need Help to migrate data from XML to oracle table.
                                957539

                                Hi Odie,

                                 

                                Im using this java code

                                 

                                GRANT JAVAUSERPRIV TO apps;

                                 

                                 

                                drop table directory_list;

                                 

                                 

                                 

                                CREATE TABLE directory_list

                                       ( filename VARCHAR2(255) );

                                   

                                 

                                 

                                 

                                 

                                CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "ListDirectory" AS

                                import java.io.*;

                                import java.sql.*;

                                 

                                public class ListDirectory

                                {

                                public static void getList(String directory)

                                                   throws SQLException

                                {

                                   File path = new File( directory );

                                   String[] list = path.list();

                                   String element;

                                 

                                     for(int i = 0; i < list.length; i++)

                                     {

                                         element = list[i];

                                         #sql { INSERT INTO directory_list (filename)

                                                VALUES (:element) };

                                     }

                                }

                                 

                                }

                                 

                                 

                                CREATE OR REPLACE PROCEDURE get_directory_list(p_directory IN VARCHAR2) AS

                                   LANGUAGE JAVA NAME 'ListDirectory.getList( java.lang.String )';

                                  

                                 

                                 

                                EXEC  get_directory_list( '//AARAV' );

                                 

                                 

                                 

                                 

                                truncate table directory_list;

                                 

                                 

                                 

                                 

                                SELECT filename

                                  FROM directory_list

                                -- WHERE 1=1;

                                 

                                 

                                Here im able to trace the file name but when i use this code in mysql it dsnt insert can you help....

                                 

                                im pasting the code as follows.

                                 

                                 

                                /* Formatted on 2013/07/02 15:53 (Formatter Plus v4.8.8) */

                                CREATE OR REPLACE PACKAGE BODY apps.xxopm_outbound

                                AS

                                   PROCEDURE main

                                /*ADVICE(4): Procedure has no parameters [516] */

                                 

                                 

                                   AS

                                      lv_file_name

                                /*ADVICE(8): Unreferenced variable [553] */

                                                     VARCHAR2 (30);

                                   BEGIN

                                      xxopm_outbound.get_directory_list ('//AARAV');

                                      xxopm_outbound.xxopm_table_insert_inb;

                                      COMMIT;

                                   END

                                /*ADVICE(15): END of program unit, package or type is not labeled [408] */

                                      ;

                                 

                                 

                                   PROCEDURE get_directory_list (p_directory

                                /*ADVICE(19): Unreferenced parameter [552] */

                                   IN VARCHAR2)

                                   AS

                                      LANGUAGE JAVA

                                      NAME 'ListDirectory.getList( java.lang.String )';

                                 

                                 

                                   PROCEDURE xxopm_table_insert_inb

                                /*ADVICE(26): Procedure has no parameters [516] */

                                 

                                 

                                   AS

                                      gv_inbox_dir

                                /*ADVICE(30): This item should be defined in a deeper scope [558] */

                                                          VARCHAR2 (20) := 'XX_AARAV';

                                      lv_insert_flag

                                /*ADVICE(33): Unreferenced variable [553] */

                                                          VARCHAR2 (20) := NULL;

                                /*ADVICE(35): Initialization to NULL is superfluous [417] */

                                 

                                 

                                      lv_update_flag

                                /*ADVICE(38): Unreferenced variable [553] */

                                                          VARCHAR2 (20) := NULL;

                                /*ADVICE(40): Initialization to NULL is superfluous [417] */

                                 

                                 

                                      lv_update_message

                                /*ADVICE(43): Unreferenced variable [553] */

                                                          VARCHAR2 (20) := NULL;

                                /*ADVICE(45): Initialization to NULL is superfluous [417] */

                                 

                                 

                                      lv_batchname

                                /*ADVICE(48): Unreferenced variable [553] */

                                                          VARCHAR2 (20);

                                      lv_count

                                /*ADVICE(51): Unreferenced variable [553] */

                                                          NUMBER;

                                /*ADVICE(53): NUMBER has no precision [315] */

                                 

                                 

                                      lv_file_name

                                /*ADVICE(56): This item should be defined in a deeper scope [558] */

                                                          VARCHAR2 (30);

                                 

                                 

                                      CURSOR c_get_batch_details

                                      IS

                                         SELECT batchname, batchstatus, subbatchname, subbatchstatus,

                                                ingredient, dispensedqty, mdispensedqty, FINAL, insert_flag,

                                                update_flag, update_message

                                           FROM xxopm_inb_main_tab

                                          WHERE insert_flag = 'Y';

                                   BEGIN

                                      FOR get_batch IN c_get_batch_details

                                      LOOP

                                         DBMS_OUTPUT.put_line ('The BATCH NAME IS ' || get_batch.batchname);

                                      END LOOP;

                                 

                                 

                                      BEGIN

                                         DBMS_OUTPUT.put_line (gv_inbox_dir);

                                 

                                 

                                         -- DBMS_OUTPUT.put_line(lv_file_name);

                                         SELECT filename

                                           INTO lv_file_name

                                           FROM directory_list

                                          WHERE 1 = 1

                                          and rownum <2;

                                 

                                 

                                         DBMS_OUTPUT.put_line ('The File Name Is :' || lv_file_name);

                                        

                                insert into  xxopm_inb_main_tab

                                SELECT A.BatchName

                                    , A.BatchStatus

                                    , B.SubBatchName

                                    , B.SubBatchStatus

                                    , C.Ingredient

                                    , C.DispensedQty

                                    , C.MDispensedQty

                                    , A.FINAL

                                    , Null

                                    , Null

                                    , Null

                                FROM XMLTable('/TransferXML'

                                      PASSING xmltype(bfilename('gv_inbox_dir ','lv_file_name'), nls_charset_id('AL32UTF8'))

                                      COLUMNS BatchName    VARCHAR2(99)  path 'BatchName'

                                            , BatchStatus  VARCHAR2(999) path 'BatchStatus'

                                            , Final        VARCHAR2(99)  path 'Final'

                                            , SubBatchList XMLTYPE       path 'SubBatch'

                                    ) A

                                  , XMLTable('/SubBatch'

                                      PASSING SubBatchList

                                      COLUMNS SubBatchName      VARCHAR2(99) path 'SubBatchName'

                                            , SubBatchStatus    VARCHAR2(99) path 'SubBatchStatus'

                                            , SubBatchDetailList XMLTYPE     path 'SubBatchDetail'

                                    ) B

                                  , XMLTable('/SubBatchDetail'

                                      PASSING SubBatchDetailList

                                      COLUMNS Ingredient    VARCHAR2(99) path 'Ingredient'

                                            , DispensedQty  VARCHAR2(99) path 'DispensedQty'

                                            , MDispensedQty VARCHAR2(99) path 'MDispensedQty'

                                    ) C;

                                 

                                 

                                                  UPDATE xxopm_inb_main_tab

                                                  SET insert_flag = 'Y';

                                 

                                 

                                               COMMIT;

                                              

                                      END;

                                   EXCEPTION

                                      WHEN OTHERS

                                      THEN

                                         UPDATE xxopm_inb_main_tab

                                            SET insert_flag = 'E';

                                /*ADVICE(124): Use of DELETE or UPDATE without WHERE clause [313] */

                                 

                                 

                                 

                                 

                                         COMMIT;

                                         DBMS_OUTPUT.put_line ('Failed to Update the Main Table');

                                /*ADVICE(129): A WHEN OTHERS clause is used in the exception section without

                                              any other specific handlers [201] */

                                 

                                 

                                   END

                                /*ADVICE(133): END of program unit, package or type is not labeled [408] */

                                      ;

                                END xxopm_outbound;

                                • 13. Re: Need Help to migrate data from XML to oracle table.
                                  odie_63

                                  I would make DIRECTORY_LIST a GLOBAL TEMPORARY TABLE instead, so that you don't have to care about deleting it, or whether someone else uses it at the same time.

                                   

                                  Your exception handler is quite bad, remove it entirely or at least display the error message so you actually know what happened.

                                   

                                  I'm guessing the problem comes from this : bfilename('gv_inbox_dir ','lv_file_name')

                                  Shouldn't it be variables instead of strings?

                                  • 14. Re: Need Help to migrate data from XML to oracle table.
                                    957539

                                    Im not using it as a DIRECTORY_LIST a GLOBAL TEMPORARY TABLE because after commit it gets deleted.

                                     

                                    And i need it for future reference.

                                     

                                    N i think your right its coming from this : bfilename('gv_inbox_dir ','lv_file_name')

                                     

                                    So can you tell me what should i change because i have made changes to the exceptions also.

                                     

                                    N im using it as a variable if you check my code.

                                    1 2 Previous Next