This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jun 26, 2012 9:17 PM by 940216 RSS

Read XML File with Header and Trailer on Unix from PL SQL

940216 Newbie
Currently Being Moderated
hi experts,

i have xml file format like this;

<BATCHHEADER>00001,20100121</BATCHHEADER>
<?xml version="1.0" encoding="UTF-8"?>
<test1>
<test11>
-- some xml1 --
</test11>
</test1>
<?xml version="1.0" encoding="UTF-8"?>
<test2>
<test21>
-- some xml2 --
</test21>
</test2>
<BATCHTRAILER>2</BATCHTRAILER>

i need to insert the data into an oracle table;

create table tmp_xml of xmltype;

insert into tmp_xml values(
xmltype (BFILENAME ('TEST_DIR', 'TMP_FILE_0001'),
NLS_CHARSET_ID ('AL32UTF8'))
);

when i try to insert it shows the below error message;

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00209: PI names starting with XML are reserved
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 295
ORA-06512: at line 1

i would like to know is there any alternative approach?
  • 1. Re: Read XML File with Header and Trailer on Unix from PL SQL
    odie_63 Guru
    Currently Being Moderated
    Hi,
    i have xml file format like this;
    Unfortunately, that's not a wellformed XML, so you can't insert the whole file in an XMLType table.

    You must isolate these two parts and treat them separately :
    <?xml version="1.0" encoding="UTF-8"?>
    <test1>
    <test11>
    -- some xml1 --
    </test11>
    </test1>
    and
    <?xml version="1.0" encoding="UTF-8"?>
    <test2>
    <test21>
    -- some xml2 --
    </test21>
    </test2>
    Can you explain what you were intending to do with the content once loaded in the table?

    Please also give your database version (SELECT * FROM v$version).
  • 2. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    hi odie,
    thanks for the reply;

    Steps:
    1. i will get xml file in format which i mentioned above.
    2. need to generate xml file into readble format.
    3. need to display readable format in output.

    for example:
    test1 test11 test2 test21
    ------- ---------- --------- -----------

    SELECT * FROM v$version

    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE     10.2.0.5.0     Production
    TNS for Solaris: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production

    Question> how to generate header and trailer into readble format?
  • 3. Re: Read XML File with Header and Trailer on Unix from PL SQL
    odie_63 Guru
    Currently Being Moderated
    Sorry, I don't understand what you want to do.

    Could you explain again, in detailed steps, and with sample data? Thanks.

    One thing is sure though, you won't be able to use XML features on your input file (since it's not XML), you'll have to preprocess it.
  • 4. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    hi odie,

    sorry for confusion;

    Step 1. i will receive sample xml in following format;
    <BATCHHEADER>00001,20120420</BATCHHEADER>
    <?xml version="1.0" encoding="UTF-8"?>
    <ABC>
    <EFGSvcRq>
    <RqUID>e011000a-1agg-477j</RqUID>
    <XferAddRs>
    <Status>
    <Severity>Info</Severity>
    <PmtRefId>04122012-8094498</PmtRefId>
    <StatusDesc>Delivered by ABC as ACK REF: 118147</StatusDesc>
    <ErrorDesc></ErrorDesc>
    <AsOfDate>2012-05-25</AsOfDate>
    <AsOfTime>12:07:20</AsOfTime>
    </Status>
    <RqUID>31aec38d-7fac-4a89-88ba-4fb74a57b8a5</RqUID>
    <SPRefId>Acknowledged</SPRefId>
    </XferAddRs>
    </EFGSvcRq>
    </ABC>
    <?xml version="1.0" encoding="UTF-8"?>
    <ABC>
    <EFGSvcRq>
    <RqUID>fa18c45a-a1f2</RqUID>
    <XferAddRs>
    <Status>
    <Severity>Info</Severity>
    <PmtRefId>04122012-8094498</PmtRefId>
    <StatusDesc>Delivered by ABC as ACK REF: 118147 CHECK NUMBER: 1000 PRINTER ID: reisdfw01ssvc01DFW01-B01-F03-ABC </StatusDesc>
    <ErrorDesc></ErrorDesc>
    <AsOfDate>2012-05-25</AsOfDate>
    <AsOfTime>12:07:26</AsOfTime>
    </Status>
    <RqUID>b4256336-7bf3</RqUID>
    <SPRefId>Final Confirmation</SPRefId>
    </XferAddRs>
    </EFGSvcRq>
    </ABC>
    <BATCHTRAILER>2</BATCHTRAILER>

    Step2: i need to generate above xml file in some kind output format (reporting to end user):
    {for this i need to read above xml file and place it in temporary table, from temporary table i will pick the records and display}.
    [example select BATCHHEADER,RqUID,Severity, BATCHTRAILER from temporary table]

    BATCHHEADER
    ------------------
    00001,20120420

    RqUID
    ----------------------- .... so on
    e011000a-1agg-477j


    Severity
    -------------- .... so on.
    Info


    BATCHTRAILER
    ---------------
    2

    is this possible, if possible i need your assistance;

    thanks
  • 5. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    hi odie,

    please ignore above message, this is the updated one

    sorry for confusion;

    Step 1. i will receive sample xml in following format;
    <BATCHHEADER>00001,20120420</BATCHHEADER>
    <?xml version="1.0" encoding="UTF-8"?>
    <ABC>
    <EFGSvcRq>
    <RqUID>e011000a-1agg-477j</RqUID>
    <XferAddRs>
    <Status>
    <Severity>Info</Severity>
    <PmtRefId>04122012-8094498</PmtRefId>
    <StatusDesc>Delivered by ABC as ACK REF: 118147</StatusDesc>
    <ErrorDesc></ErrorDesc>
    <AsOfDate>2012-05-25</AsOfDate>
    <AsOfTime>12:07:20</AsOfTime>
    </Status>
    <RqUID>31aec38d-7fac-4a89-88ba-4fb74a57b8a5</RqUID>
    <SPRefId>Acknowledged</SPRefId>
    </XferAddRs>
    </EFGSvcRq>
    </ABC>
    <?xml version="1.0" encoding="UTF-8"?>
    <ABC>
    <EFGSvcRq>
    <RqUID>fa18c45a-a1f2</RqUID>
    <XferAddRs>
    <Status>
    <Severity>Info</Severity>
    <PmtRefId>04122012-8094498</PmtRefId>
    <StatusDesc>Delivered by ABC as ACK REF: 118147 CHECK NUMBER: 1000 PRINTER ID: reisdfw01ssvc01DFW01-B01-F03-ABC </StatusDesc>
    <ErrorDesc></ErrorDesc>
    <AsOfDate>2012-05-25</AsOfDate>
    <AsOfTime>12:07:26</AsOfTime>
    </Status>
    <RqUID>b4256336-7bf3</RqUID>
    <SPRefId>Final Confirmation</SPRefId>
    </XferAddRs>
    </EFGSvcRq>
    </ABC>
    <BATCHTRAILER>2</BATCHTRAILER>

    Step2: i need to generate above xml file in some kind output format (reporting to end user):
    {for this i need to read above xml file and place it in temporary table, from temporary table i will pick the records and display}.
    [example select BATCHHEADER,RqUID,Severity, BATCHTRAILER from temporary table]

    BATCHHEADER
    ------------------
    00001,20120420

    RqUID
    -----------------------
    e011000a-1agg-477j


    Severity
    --------------
    Info


    BATCHTRAILER
    ---------------
    2

    is this possible, if possible i need your assistance;

    thanks
  • 6. Re: Read XML File with Header and Trailer on Unix from PL SQL
    odie_63 Guru
    Currently Being Moderated
    Step2: i need to generate above xml file in some kind output format (reporting to end user):
    {for this i need to read above xml file and place it in temporary table, from temporary table i will pick the records and display}.
    Easier said than done... ;)

    As already explained, your file is not XML. The fact that it contains XML tags doesn't make it a valid XML file/document, no standard XML parser will be able to process its content.
    Out of curiosity, what's the source of the file?

    So, to make it short, you have to build your own text parser that complies with this specific format.
    This parser must :
    - locate header info and retrieve it
    - locate trailer info and retrieve it
    - locate each XML prolog (the <?xml version= ...?> part) embedded in the structure and extract the required data from the corresponding XML content.

    Good news is once we've isolated each XML documents, ie each <?xml ... ?><ABC> ... </ABC>, we can process it as an XMLType since it's now a valid document.

    Here's an attempt using regular expressions to parse the text :

    This is the parser :
    create or replace package XFileHandler as
    
      type TRecord is record (
        HEADER   varchar2(100)
      , TRAILER  varchar2(100)
      , RqUID    varchar2(30)
      , Severity varchar2(30)
      );
    
      type TRecordTable is table of TRecord;
    
      function getRows (p_directory in varchar2, p_filename in varchar2) return TRecordTable pipelined;
    
    end;
    /
    
    create or replace package body XFileHandler is
    
      function getRows (p_directory in varchar2, p_filename in varchar2)
       return TRecordTable pipelined
      is
    
        nb_rec          number := 1;
        tmp_xml         clob;
        tmp_file        clob;
        rec             TRecord;
    
      begin
    
        dbms_lob.createtemporary(tmp_file, true);
        tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);
    
        rec.HEADER := regexp_replace(tmp_file, '.*<BATCHHEADER>(.*)</BATCHHEADER>.*', '\1', 1, 1, 'n');
        rec.TRAILER := regexp_replace(tmp_file, '.*<BATCHTRAILER>(.*)</BATCHTRAILER>.*', '\1', 1, 1, 'n');
    
        loop
    
          -- this regexp finds occurrence(s) of this pattern : "<?xml ... ?><root_tag> ... </root_tag>"
          tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s+<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
          exit when length(tmp_xml) = 0;
          --dbms_output.put_line(tmp_rec);
          nb_rec := nb_rec + 1;
    
          select RqUID, Severity
          into rec.RqUID
             , rec.Severity
          from xmltable(
                 '/ABC/EFGSvcRq' passing xmltype(tmp_xml)
                 columns RqUID    varchar2(30) path 'RqUID'
                       , Severity varchar2(30) path 'XferAddRs/Status/Severity'
               )
          ;
    
          pipe row ( rec );
    
        end loop;
    
        dbms_lob.freetemporary(tmp_file);
    
      end;
    
    end;
    /
    And its usage :
    SQL> select * from table(XFileHandler.getRows('TEST_DIR', 'sample.txt'));
    
    HEADER                    TRAILER           RQUID                          SEVERITY
    ------------------------- ----------------- ------------------------------ ------------------------------
    00001,20120420            2                 e011000a-1agg-477j             Info
    00001,20120420            2                 fa18c45a-a1f2                  Info
     
  • 7. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    Excellent work odie, thanks for your efforts;
    it worked;

    but here i have one type of problem, end user changed the apperance of file format,
    data will be same, but it is continous (please see below);

    <BATCHHEADER>00002,20120525</BATCHHEADER><?xml version="1.0" encoding="UTF-8"?><ABC><EFGSvcRq><RqUID>e724427a-1aff</RqUID><XferAddRs><Status><Severity>Info</Severity><PmtRefId>04122012-8094498</PmtRefId><StatusDesc>Delivered by ABC as ACK REF: 118147</StatusDesc><ErrorDesc></ErrorDesc><AsOfDate>2012-05-25</AsOfDate><AsOfTime>12:07:20</AsOfTime></Status><RqUID>31aec38d-7fac</RqUID><SPRefId>Acknowledged</SPRefId></XferAddRs></EFGSvcRq></ABC><?xml version="1.0" encoding="UTF-8"?><ABC><EFGSvcRq><RqUID>fa18c45a-a1f2</RqUID><XferAddRs><Status><Severity>Info</Severity><PmtRefId>04122012-8094498</PmtRefId><StatusDesc>Delivered by ABC as ACK REF: 118147 CHECK NUMBER: 1000 PRINTER ID: reisdfw01ssvc01DFW01-B01-F03-ABC</StatusDesc><ErrorDesc></ErrorDesc><AsOfDate>2012-05-25</AsOfDate><AsOfTime>12:07:26</AsOfTime></Status><RqUID>b4256336-7bf3</RqUID><SPRefId>Final Confirmation</SPRefId></XferAddRs></EFGSvcRq></ABC><BATCHTRAILER>2</BATCHTRAILER>

    Problem: when i used above program, it does not return any rows;
    need your assistance!

    File Name looks like: TABCO.ABCD_TST.RESP.20120521.00002;

    when is save file name: TABCO.ABCD_TST.RESP.20120521 with below file type
    File Type: 00002 File (.00002)

    select * from v$version;
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE     10.2.0.5.0     Production
    TNS for Solaris: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production
  • 8. Re: Read XML File with Header and Trailer on Unix from PL SQL
    odie_63 Guru
    Currently Being Moderated
    Problem: when i used above program, it does not return any rows;
    Indeed.
    That's because the regular expression used to extract each embedded XML expects at least one whitespace between the prolog and the root tag :
    <\?xml[^?]+\?>\s+<([^>]+)>.*?</\1>
                    ^
                    here
    We must change it to \s* to account for 0 or more occurrences.

    Here's the new package body (added a missing return statement in the function too) :
    create or replace package body XFileHandler is
    
      function getRows (p_directory in varchar2, p_filename in varchar2)
       return TRecordTable pipelined
      is
    
        nb_rec          number := 1;
        tmp_xml         clob;
        tmp_file        clob;
        rec             TRecord;
    
      begin
    
        dbms_lob.createtemporary(tmp_file, true);
        tmp_file := dbms_xslprocessor.read2clob(p_directory, p_filename);
    
        rec.HEADER := regexp_replace(tmp_file, '.*<BATCHHEADER>(.*)</BATCHHEADER>.*', '\1', 1, 1, 'n');
        rec.TRAILER := regexp_replace(tmp_file, '.*<BATCHTRAILER>(.*)</BATCHTRAILER>.*', '\1', 1, 1, 'n');
    
        loop
    
          tmp_xml := regexp_substr(tmp_file, '<\?xml[^?]+\?>\s*<([^>]+)>.*?</\1>', 1, nb_rec, 'n');
          exit when length(tmp_xml) = 0;
          --dbms_output.put_line(tmp_rec);
          nb_rec := nb_rec + 1;
    
          select RqUID, Severity
          into rec.RqUID
             , rec.Severity
          from xmltable(
                 '/ABC/EFGSvcRq' passing xmltype(tmp_xml)
                 columns RqUID    varchar2(30) path 'RqUID'
                       , Severity varchar2(30) path 'XferAddRs/Status/Severity'
               )
          ;
    
          pipe row ( rec );
    
        end loop;
    
        dbms_lob.freetemporary(tmp_file);
        
        return;
    
      end;
    
    end;
  • 9. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    Odie, you are Genius; it worked again;
    Thank you so much;

    i am Interested to learn more on these; let me know if you have any documents, websites(links).. etc;
  • 10. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    Thanks Odie.
  • 11. Re: Read XML File with Header and Trailer on Unix from PL SQL
    odie_63 Guru
    Currently Being Moderated
    i am Interested to learn more on these; let me know if you have any documents, websites(links).. etc;
    Well, there are different concepts involved in the package, all covered by the documentation :

    - Regular expressions : http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm
    - Pipelined functions : http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#i52932
    - XML support : http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/toc.htm
  • 12. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    hi odie,

    one more change from user;

    previously i created directory in data base server and submit above program which works successfully;

    but now they don't want any directory, they need pass path name as parameter(as input);


    my file path will be
    /abc/cde/fghi/
    from application server (not from data base server);

    when i tried to submit the program;
    it shows the below error message;

    stat_low = 8B
    stat_high = 0
    emsg:was terminated by signal 11

    Note: i am using oracle report from report i call the above package;

    does this program will work with file path also?

    thanks
  • 13. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    new change from user
  • 14. Re: Read XML File with Header and Trailer on Unix from PL SQL
    940216 Newbie
    Currently Being Moderated
    hi odie,

    my made changes;
    your program works perfectly; thanks once again;
1 2 Previous Next

Legend

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