14 Replies Latest reply: Jun 28, 2013 9:20 PM by 9d2e0d8c-538a-468f-93ad-4b93dd1c3b46 RSS

    Load xml data in Oracle table

    N@*841964*
      Hi,

      I've already gone through few threads in forums itself, but for my requirement nothing comes closer, I am posting my query. I've one xml file like this
      <?xml version="1.0"?>
      <ACCOUNT_HEADER_ACK>
      <HEADER>
      <STATUS_CODE>100</STATUS_CODE>
      <STATUS_REMARKS>check</STATUS_REMARKS>
      </HEADER>
      <DETAILS>
      <DETAIL>
      <SEGMENT_NUMBER>2</SEGMENT_NUMBER>
      <REMARKS>rp polytechnic</REMARKS>
      </DETAIL>
      <DETAIL>
      <SEGMENT_NUMBER>3</SEGMENT_NUMBER>
      <REMARKS>rp polytechnic administration</REMARKS>
      </DETAIL>
      <DETAIL>
      <SEGMENT_NUMBER>4</SEGMENT_NUMBER>
      <REMARKS>rp polytechnic finance</REMARKS>
      </DETAIL>
      <DETAIL>
      <SEGMENT_NUMBER>5</SEGMENT_NUMBER>
      <REMARKS>rp polytechnic logistics</REMARKS>
      </DETAIL>
      </DETAILS>
      <HEADER>
      <STATUS_CODE>500</STATUS_CODE>
      <STATUS_REMARKS>process exception</STATUS_REMARKS>
      </HEADER>
      <DETAILS>
      <DETAIL>
      <SEGMENT_NUMBER>20</SEGMENT_NUMBER>
      <REMARKS> base polytechnic</REMARKS>
      </DETAIL>
      <DETAIL>
      <SEGMENT_NUMBER>30</SEGMENT_NUMBER>
      </DETAIL>
      <DETAIL>
      <SEGMENT_NUMBER>40</SEGMENT_NUMBER>
      <REMARKS> base polytechnic finance</REMARKS>
      </DETAIL>
      <DETAIL>
      <SEGMENT_NUMBER>50</SEGMENT_NUMBER>
      <REMARKS> base polytechnic logistics</REMARKS>
      </DETAIL>
      </DETAILS>
      </ACCOUNT_HEADER_ACK>

      Here the xml structure is like Master and child structure I want to insert that data into Oracle tables by using the sql*loader initally I tried to created one control file but in the control file I don't know how to terminate, so I created two control files

      load data
      infile 'acct.xml' "str '</DETAIL>'"
      truncate
      into table xxrp_acct_detail
      TRAILING NULLCOLS
      (
      dummy filler terminated by "<DETAIL>",
      SEGMENT_NUMBER enclosed by "<SEGMENT_NUMBER>" and "</SEGMENT_NUMBER>",
      REMARKS enclosed by "<REMARKS>" and "</REMARKS>"
      )


      load data
      infile acct.xml' "str '</HEADER>'"
      truncate
      into table xxrp_acct_header
      fields terminated by "<HEADER>"
      TRAILING NULLCOLS
      (
      dummy filler terminated by "<HEADER>",
      STATUS_CODE enclosed by "<STATUS_CODE>" and "</STATUS_CODE>",
      STATUS_REMARKS enclosed by "<STATUS_REMARKS>" and "</STATUS_REMARKS>"
      )

      I am referring the same xml file in both the control files, where as for the for the first control file I was able to load the records but the second which I assume as header table not able to load the rest records. I am getting the below mentioned error.

      Record 2: Rejected - Error on table XXRP_ACCT_HEADER, column DUMMY.
      Field in data file exceeds maximum length
      Record 3: Rejected - Error on table XXRP_ACCT_HEADER, column DUMMY.
      Field in data file exceeds maximum length

      Actually if its possible to seggrate in one control file then it will be helpful for me. I am also open for the external table option also. Please help me in this regard.

      Thanks in advance.

      Regards
      Nagendra
        • 1. Re: Load xml data in Oracle table
          odie_63
          Hi,

          Using SQL*Loader as an XML parser is not what I'd call a good idea.

          How about using built-in XML functionalities in the database?

          What's your database version? (select * from v$version)


          Here's an example working on 10.2 and upwards :
          SQL> create directory test_dir as 'c:\ora11\test';
           
          Directory created
           
          SQL> 
          SQL> create table xxrp_acct_detail (
            2   segment_number number,
            3   remarks        varchar2(100)
            4  );
           
          Table created
           
          SQL> 
          SQL> create table xxrp_acct_header (
            2   status_code    number,
            3   status_remarks varchar2(100)
            4  );
           
          Table created
           
          SQL> 
          SQL> DECLARE
            2  
            3   acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml')
            4                              , nls_charset_id('AL32UTF8') );
            5  
            6  BEGIN
            7  
            8   insert into xxrp_acct_header (status_code, status_remarks)
            9   select *
           10   from xmltable(
           11    '/ACCOUNT_HEADER_ACK/HEADER'
           12    passing acct_doc
           13    columns status_code    number        path 'STATUS_CODE',
           14            status_remarks varchar2(100) path 'STATUS_REMARKS'
           15   );
           16  
           17   insert into xxrp_acct_detail (segment_number, remarks)
           18   select *
           19   from xmltable(
           20    '/ACCOUNT_HEADER_ACK/DETAILS/DETAIL'
           21    passing acct_doc
           22    columns segment_number number        path 'SEGMENT_NUMBER',
           23            remarks        varchar2(100) path 'REMARKS'
           24   );
           25  
           26  END;
           27  /
           
          PL/SQL procedure successfully completed
           
          SQL> select * from xxrp_acct_header;
           
          STATUS_CODE STATUS_REMARKS
          ----------- --------------------------------------------------------------------------------
                  100 check
                  500 process exception
           
          SQL> select * from xxrp_acct_detail;
           
          SEGMENT_NUMBER REMARKS
          -------------- --------------------------------------------------------------------------------
                       2 rp polytechnic
                       3 rp polytechnic administration
                       4 rp polytechnic finance
                       5 rp polytechnic logistics
                      20  base polytechnic
                      30 
                      40  base polytechnic finance
                      50  base polytechnic logistics
           
          • 2. Re: Load xml data in Oracle table
            N@*841964*
            Hi odie,

            I've read your earlier resolved messages/threads where you have exceptionally helped out many of guys like me. Hats off for that !!

            Now in my below requirement acutally in begining I was in fact in thought of making it two tables so that it helps but the then I will be missing the link between the two tables I regret to my own solution. Can you please help me out in giving the solution like this. I am totally newbie to xml kind integration platform.

            The proposed one is like this

            The table (combined two tables into single so that I can have refernce of each column), my db version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

            create table xxrp_acct_details(
            status_code number,
            status_remarks varchar2(100),
            segment_number number,
            remarks varchar2(100)
            );

            And reading the same 'acct.xml ' I need to write a script

            Now my final result should fetch me like this

            select * from xxrp_acct_details

            Statuscode status remarks segement remarks
            --------------- -------------------- ------------- ------------
            100 check 2 rp polytechnic
            100 check 3 rp polytechnic administration
            100 check 4 rp polytechnic finance
            100 check 5 rp polytechnic logistics
            500 process exception 20 base polytechnic
            500 process exception 30
            500 process exception 40 base polytechnic finance
            500 process exception 50 base polytechnic logistics

            Please let me know how can I write pl/sql script for this scenario.

            Thanks in Advance for your great help!!!


            Regards
            Nagendra
            • 3. Re: Load xml data in Oracle table
              odie_63
              Here are two possible solutions :

              1) Reading headers and details using two separate XMLTables :
              DECLARE
              
               acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );
              
              BEGIN
              
               insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks)
               select x1.status_code, 
                      x1.status_remarks,
                      x2.segment_number, 
                      x2.remarks
               from xmltable(
                '/ACCOUNT_HEADER_ACK/HEADER'
                passing acct_doc
                columns header_no      for ordinality,
                        status_code    number        path 'STATUS_CODE',
                        status_remarks varchar2(100) path 'STATUS_REMARKS'
               ) x1,
               xmltable(
                '$d/ACCOUNT_HEADER_ACK/DETAILS[$hn]/DETAIL'
                passing acct_doc as "d",
                        x1.header_no as "hn"
                columns segment_number number        path 'SEGMENT_NUMBER',
                        remarks        varchar2(100) path 'REMARKS'
               ) x2
               ;
              
              END;
              The first one (aliased X1) extracts all headers into separate rows. The generated column HEADER_NO is used to keep track of the header rank inside the document.
              Then we join a second XMLTable (X2), passing it HEADER_NO, so that we can access the corresponding DETAIL elements.

              2) Reading with a single XMLTable, but a little more complex XQuery :
              DECLARE
              
               acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );
              
              BEGIN
              
               insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks)
               select x.*
               from xmltable(
                'for $i in /ACCOUNT_HEADER_ACK/HEADER
                 return 
                  for $j in $i/following-sibling::DETAILS[1]/DETAIL
                  return element r {$i, $j}'
                passing acct_doc
                columns status_code    number        path 'HEADER/STATUS_CODE',
                        status_remarks varchar2(100) path 'HEADER/STATUS_REMARKS',
                        segment_number number        path 'DETAIL/SEGMENT_NUMBER',
                        remarks        varchar2(100) path 'DETAIL/REMARKS'
               ) x
               ;
              
              END;
              Here, we use an XQuery to extract the info we need.
              Basically, it's the same logic as above but with two nested loops that access each HEADER, then each DETAILS located immediately after in document order.

              Here's the link to the documentation regarding XMLTable and XQuery in Oracle :
              http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAGCBGJ
              • 4. Re: Load xml data in Oracle table
                N@*841964*
                Hi Odie,

                Its really brilliant piece of work from your side. It was nearly matching my solution. I will use the same logic for rest of the programs, if in case of any doubts in further I will seek your help ..:).

                Once again thanks for your great effort.


                Regards
                Nagendra
                • 5. Re: Load xml data in Oracle table
                  844560
                  Hi Odie,

                  I appreciate your suggestions..!
                  well UI am facing an issue with loading xml data in to oracle table...
                  the problem here is there are elements repeating in xmls and but its not consistantl.

                  to get the data from repeating elements I wrote a procedure as follows

                  create or replace PROCEDURE xyz is
                  --declaring a cursor

                  CURSOR abc_CURSOR IS

                  select CLOB_ID

                  from table_CLOB ;

                  var_error_id NUMBER(22);

                  var_error_data varchar2(4000);

                  cnt INTEGER(2);

                  BEGIN

                  --opening a cursor

                  open abc_CURSOR;

                  LOOP

                  --fetching records from a cursor

                  fetch abc_CURSOR into var_error_id;
                  --cnt := 0

                  --loop
                  --cnt:= cnt+1;
                  for cnt in 1..3 loop

                  SELECT CLOB_ID
                  /*||'|'||xmltype(tnameclob).extract('array/errInfo[cnt]/text()').getStringVal()
                  ||'|'||xmltype(tname_clob).extract('array/errInfo[1]/errorCode/text()').getStringVal()
                  |'|'||xmltype(tname_clob).extract('array/errInfo[1]/type/text()').getStringVal() */
                  ||'|'||xmltype(tname_clob).extract('array/errInfo[cnt]/Replaced/text()').getStringVal()
                  ||'|'||xmltype(tname_clob).extract('array/errInfo[cnt]/sentCode/text()').getStringVal()
                  ||'|'||KEY
                  into var_error_data
                  FROM (SELECT CLOB_ID,
                  Nvl((REGEXP_REPLACE(tname_CLOB,'[^'||CHR (32)||'-'||CHR(127)||']','')),'<?xml version="1.0" encoding="UTF-8"?> <NULL> </NULL>')tname_CLOB,
                  KEY FROM clob e where clob_id = var_error_id )A
                  ;

                  DBMS_OUTPUT.put_line(cnt);
                  DBMS_OUTPUT.put_line(var_error_data);

                  --end loop;

                  --testing exit conditions

                  EXIT when abc_CURSOR%NOTFOUND;

                  END LOOP;

                  --closing the cursor

                  close abc_CURSOR;

                  DBMS_OUTPUT.put_line('DONE');

                  END;


                  but the the problem ..if I entered [1] in the sql I am getting all the first elements ..if I enter [2] .. getting 2nd elements..
                  but if I enter [cnt] ..xml is not parsing and getting null values.. i.e

                  |784910||||2011-02-24 14:29:05|13872576
                  1
                  784913||||2011-02-24 14:29:07|13872583
                  2
                  784919||||2011-02-24 14:29:14|13872598
                  3

                  could you please let me know what was my mistake in it..
                  • 6. Re: Load xml data in Oracle table
                    861238
                    Odie - ok, safe to say "you da man"... how does this method perform on very large (10-15MB) xml files? I just wish Oracle would update sql*loader to ingest xml as the data source... would make this 10,000x easier... :)
                    • 7. Re: Load xml data in Oracle table
                      odie_63
                      how does this method perform on very large (10-15MB) xml files?
                      See the XML DB FAQ here : XML DB FAQ

                      Object-Relational and binary XML storages are optimized for efficient loading and data access.
                      • 8. Re: Load xml data in Oracle table
                        Raj Rammohan
                        Hi odie,

                        it's a very nice explanation. anybody can understand what's that

                        will you please tell me how we can do the same in Oracle 9i EE 9.2.0.8.0.

                        Thanks a lot
                        Ram
                        • 9. Re: Load xml data in Oracle table
                          user12187987
                          Hi,
                          It is nice coding.
                          because I am working in xml data file uploading process.
                          so thanks for very good documents.

                          Thanks & Regards

                          Ram Babu Mandal
                          09958422606
                          • 10. Re: Load xml data in Oracle table
                            880561
                            Hi Odie,

                            I am getting the following error while executing the above piece of code:

                            ERROR at line 1:
                            ORA-22288: file or LOB operation FILEOPEN failed
                            No such file or directory
                            ORA-06512: at "SYS.XMLTYPE", line 296
                            ORA-06512: at line 3

                            Could you please help me in this regard
                            • 11. Re: Load xml data in Oracle table
                              odie_63
                              I am getting the following error while executing the above piece of code
                              Which piece of code?
                              • 12. Re: Load xml data in Oracle table
                                880561
                                Hi Odie,

                                Thanks very much for such wonderful solution.

                                I have successfully used your piece of code and implemented my requirement. But I want to pass the file name dynamically. ie., in the 'TEST_DIR', i want to pick all the .xml files in the 'TEST_DIR'.

                                I dont know how to implement this. Could you plz help me in this regard.


                                Thanks in Advance.
                                Mr.KK
                                • 13. Re: Load xml data in Oracle table
                                  odie_63
                                  Hi,

                                  Please post in a new thread of your own.

                                  Always give your database version (select * from v$version).
                                  • 14. Re: Load xml data in Oracle table
                                    9d2e0d8c-538a-468f-93ad-4b93dd1c3b46

                                    Hi,

                                     

                                    Can somebody help me do the below query?

                                     

                                    <abc>

                                    <M Name="def" Code="A5">

                                      <L>

                                       <A/>

                                      </L>

                                    </M>

                                    <M Name="jhk" Code="B5">

                                      <L Name="xyz" Code="C5">

                                       <A Name="W1" Code="W5">

                                        <S Name="fgh"/>

                                       </A>

                                      </L>

                                    </M>

                                    </abc>


                                    I have a xmlfile like the above example, how do i do select * from xmltable so that I can get all columns loaded into a table.


                                    I want the select statement for the following table:

                                    MName Mcode  LName Lcode AName Acode sname


                                    Thanks in advance.