1 2 Previous Next 15 Replies Latest reply: Jun 26, 2012 11:17 PM by 940216 RSS

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

    940216
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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;
                          • 11. Re: Read XML File with Header and Trailer on Unix from PL SQL
                            odie_63
                            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
                              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
                                new change from user
                                • 14. Re: Read XML File with Header and Trailer on Unix from PL SQL
                                  940216
                                  hi odie,

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