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

Load xml data in Oracle table

N@*841964* Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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* Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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* Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    user147134 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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.