4 Replies Latest reply: Dec 18, 2012 4:25 AM by odie_63 RSS

    Remove <?xml version = '1.0'?>

    968361
      I have a requirement In which i have to wite XML o/p into file.
      I have used the XMLelement function to create xml and then used Utl_file.put_raw to raw the data into file.
      But the requirement was to write the output for more than 1,00,000 transactions. So i have to break the query and run a loop and to write each 10,000 records in one loop as i was running out of memory. The code is working fine but the tag <?xml version = '1.0'?> is comming after each 10,000 iteration i.e after next loop starts to process anothe bunch of 10,000 records <?xml version = '1.0'?> header again comes at the top so for 100,000 records the <?xml version = '1.0'?> is comming 10 times. As i am appending o/p of next iteration to the previous one.

      it is like <?xml version = '1.0'?>
      trans 1
      .
      .
      .
      trans 10,000
      <?xml version = '1.0'?>
      trans 1
      .
      .
      . trans 10,000
      <?xml version = '1.0'?>



      My question is how can i remove <?xml version = '1.0'?> from my xml. i tried to convert my xmltype into clob and then apply the substr but it is giving me error XML parsing failed.

      my oracle version is 10g.


      It would be great if you could suggest any possible way to remove <?xml version = '1.0'?> from header.



      Source code is

      Edited by: 965358 on Dec 17, 2012 7:32 AM
        • 1. Re: Remove <?xml version = '1.0'?>
          odie_63
          The best way is to not generate the XML prolog in the first place, instead of trying to remove it afterwards. It should be easy to achieve.

          Unless you're using XMLRoot function in the middle of the process, SQL/XML functions won't generate a prolog.
          So please post what you have so far, we'll see what's wrong and what could be improved.
          • 2. Re: Remove <?xml version = '1.0'?>
            968361
            Please see the below code. I am not using XMLROOT
            create or replace procedure test1(p_entity_ref in varchar2,
                                                            p_filepath IN VARCHAR2,
                                                            p_filename IN VARCHAR2) is
            
               -- Data Variables
               v_xml              XMLTYPE;
               v_blob             BLOB;
               v_data_length      NUMBER;
            
               -- Loop Control Variables
               v_offset           NUMBER             DEFAULT 1;
               v_chunk   CONSTANT NUMBER             DEFAULT 4000;
            
               -- UTL_FILE variables
               fh                 UTL_FILE.file_type;
               l_count number;
               MIN_AC_Entry number;
               Max_ac_entry number;
               max_limit    number;
               l_loop NUMBER ;
               
            
                MyList INT_LIST := INT_LIST();
               
              cursor Cur_AC_entry is
              Select AC_ENTRY_SR_NO
              from trans
              where entity_ref = p_entity_ref
              order by AC_ENTRY_SR_NO;
            
            BEGIN
            
             
               
              open Cur_AC_entry;
              loop
                fetch Cur_AC_entry bulk collect into MyList LIMIT 4000;
               
              
            
             EXIT WHEN MyList.count = 0;
             
             v_xml := null;
             
                      select  XMLAGG(xmlelement("transaction",
            
                                                   xmlelement("transactionnumber",
                                                              trans.Trn_Ref_No),
                                                   xmlelement("transaction_description",
                                                              trans.trn_desc),
                                                   xmlelement("date_transaction",
                                                              to_char(trans.trn_dt,
                                                                      'YYYY-MM-DD') || 'T' ||
                                                              to_char(trans.trn_dt,
                                                                      'HH24:MI:SS')),
                                                   xmlelement("teller",
                                                              trans.Maker_ID),
                                                   xmlelement("authorized",
                                                              trans.checker_id),
                                                   xmlelement("value_date",
                                                              to_char(trans.value_dt,
                                                                      'YYYY-MM-DD') || 'T' ||
                                                              to_char(trans.value_dt,
                                                                      'HH24:MI:SS')),
                                                   xmlelement("transmode_code",
                                                              trans.transaction_mode),
                                                   xmlelement("amount_local",
                                                              trans.lcy_amount),
            
                                                   CASE
                                                     WHEN source.source_party <>
                                                          'acc_with_bntb' THEN
                                                      xmlelement("t_from_my_client", xmlelement("from_funds_code", source.from_FUNDS_TYPE), xmlelement("from_foreign_currency", xmlelement("foreign_currency_code", source.FCY), xmlelement("foreign_amount", source.FCY_AMOUNT), xmlelement("foreign_exchange_rate", source.EXCH_RATE)), CASE
                                                     WHEN trans.transaction_mode <>
                                                          'Cash_Transaction' Then
                                                      xmlelement("from_account",
                                                                 xmlelement("institution_name",
                                                                            source.INSTITUTION_NAME),
            
                                                                 xmlelement("institution_code",
                                                                            source.INSTITUTION_ID),
                                                                 xmlelement("branch",
                                                                            source.AC_BRANCH),
                                                                 xmlelement("account",
                                                                            source.AC_NO),
                                                                 xmlelement("currency_code",
                                                                            source.LCY),
                                                                 xmlelement("account_name",
                                                                            source.ACCOUNT_NAME),
                                                                 xmlelement("client_number",
                                                                            source.CUST_No),
                                                                 xmlelement("personal_account_type",
                                                                            source.ACCOUNT_TYPE),
            
                                                                 xmlelement("signatory",
                                                                            xmlelement("is_primary",
                                                                                       source.is_primary),
                                                                            xmlelement("t_person",
                                                                                       xmlelement("gender",
                                                                                                  source.GENDER),
                                                                                       xmlelement("title",
                                                                                                  source.TITLE),
                                                                                       xmlelement("first_name",
                                                                                                  source.FIRST_NAME),
                                                                                       xmlelement("middle_name",
                                                                                                  source.MIDDLE_NAME),
                                                                                       xmlelement("last_name",
                                                                                                  source.LAST_NAME),
                                                                                       xmlelement("birthdate",
                                                                                                  to_char(source.DATE_OF_BIRTH,
                                                                                                          'YYYY-MM-DD') || 'T' ||
                                                                                                  to_char(source.DATE_OF_BIRTH,
                                                                                                          'HH24:MI:SS')),
                                                                                       xmlelement("nationality1",
                                                                                                  source.NATIONALITY),
            
                                                                                       xmlelement("phones",
                                                                                                  xmlelement("tph_contact_type",
                                                                                                             source.CONTACT_TYPE),
                                                                                                  xmlelement("tph_communication_type",
                                                                                                             source.COMMUNICATION_TYPE),
                                                                                                  xmlelement("tph_country_prefix",
                                                                                                             source.COUNTRY_CODE),
                                                                                                  xmlelement("tph_number",
                                                                                                             source.PHONE_NUMBER)),
                                                                                       xmlelement("addresses",
                                                                                                  xmlelement("address_type",
                                                                                                             source.ADDRESS_TYPE),
                                                                                                  xmlelement("address",
                                                                                                             source.ADDRESS),
                                                                                                  xmlelement("city",
                                                                                                             source.CITY)),
                                                                                       xmlelement("occupation",
                                                                                                  source.OCCUPATION),
            
                                                                                       xmlelement("t_person_identification",
            
                                                                                                  xmlelement("type",
                                                                                                             source.ID_TYPE),
                                                                                                  xmlelement("number",
                                                                                                             source.ID_NUMBER),
                                                                                                  xmlelement("issue_country",
                                                                                                             source.ISSUED_IN)))),
                                                                 xmlelement("opened",
                                                                            to_char(source.AC_OPEN_DATE,
                                                                                    'YYYY-MM-DD') || 'T' ||
                                                                            to_char(source.AC_OPEN_DATE,
                                                                                    'HH24:MI:SS')),
                                                                 xmlelement("balance",
                                                                            source.ACY_BALANCE),
                                                                 xmlelement("status_code",
                                                                            source.ACCOUNT_STATUS),
                                                                 xmlelement("beneficiary",
                                                                            source.BENEF_DETAILS)
            
                                                                 )
            
                                                     ELSE
            
                                                      xmlelement("from_person",
                                                                 xmlelement("gender",
                                                                            source.GENDER),
                                                                 xmlelement("title",
                                                                            source.TITLE),
                                                                 xmlelement("first_name",
                                                                            source.FIRST_NAME),
                                                                 xmlelement("middle_name",
                                                                            source.MIDDLE_NAME),
                                                                 xmlelement("last_name",
                                                                            source.LAST_NAME),
                                                                 xmlelement("birthdate",
                                                                            to_char(source.DATE_OF_BIRTH,
                                                                                    'YYYY-MM-DD') || 'T' ||
                                                                            to_char(source.DATE_OF_BIRTH,
                                                                                    'HH24:MI:SS')),
                                                                 xmlelement("nationality1",
                                                                            source.NATIONALITY),
            
                                                                 xmlelement("phones",
                                                                            xmlelement("tph_contact_type",
                                                                                       source.CONTACT_TYPE),
                                                                            xmlelement("tph_communication_type",
                                                                                       source.COMMUNICATION_TYPE),
                                                                            xmlelement("tph_country_prefix",
                                                                                       source.COUNTRY_CODE),
                                                                            xmlelement("tph_number",
                                                                                       source.PHONE_NUMBER)),
                                                                 xmlelement("addresses",
                                                                            xmlelement("address_type",
                                                                                       source.ADDRESS_TYPE),
                                                                            xmlelement("address",
                                                                                       source.ADDRESS),
                                                                            xmlelement("city",
                                                                                       source.CITY)),
                                                                  xmlelement("occupation",
                                                                            source.OCCUPATION),
            
                                                                 xmlelement("t_person_identification",
            
                                                                            xmlelement("type",
                                                                                       source.ID_TYPE),
                                                                            xmlelement("number",
                                                                                       source.ID_NUMBER),
                                                                            xmlelement("issue_country",
                                                                                       source.ISSUED_IN)))
                                                   END,
                                                   xmlelement("from_country",
                                                              source.country_code))
            
                                        Else
            
                                        xmlelement("t_from",
                                                   xmlelement("from_funds_code",
                                                              source.from_FUNDS_TYPE),
                                                   xmlelement("from_foreign_currency",
                                                              xmlelement("foreign_currency_code",
                                                                         source.FCY),
                                                              xmlelement("foreign_amount",
                                                                         source.FCY_AMOUNT),
                                                              xmlelement("foreign_exchange_rate",
                                                                         source.EXCH_RATE)),
                                                   CASE
                                                     WHEN trans.transaction_mode <>
                                                          'Cash_Transaction' Then
                                                      xmlelement("from_account",
                                                                 xmlelement("institution_name",
                                                                            source.INSTITUTION_NAME),
            
                                                                 xmlelement("institution_code",
                                                                            source.INSTITUTION_ID),
            
                                                                 xmlelement("account",
                                                                            source.AC_NO))
            
                                                     ELSE
            
                                                      xmlelement("from_person",
            
                                                                 xmlelement("first_name",
                                                                            source.FIRST_NAME),
            
                                                                 xmlelement("last_name",
                                                                            source.LAST_NAME))
                                                   end,
            
                                                   xmlelement("from_country",
                                                              source.country_code))
            
                                        END,
            
                                        CASE
                                          WHEN dest.dest_party <>
                                               'acc_with_bntb' THEN
                                           xmlelement("t_to_my_client1", xmlelement("to_funds_code", dest.To_FUNDS_TYPE), xmlelement("to_foreign_currency", xmlelement("to_foreign_currency", dest.FCY), xmlelement("foreign_amount", dest.FCY_AMOUNT), xmlelement("foreign_exchange_rate", dest.EXCH_RATE)), CASE
                                          WHEN trans.transaction_mode <>
                                               'Cash_Transaction' Then
                                           xmlelement("t_to_account",
                                                      xmlelement("institution_name",
                                                                 dest.INSTITUTION_NAME),
            
                                                      xmlelement("institution_code",
                                                                 dest.INSTITUTION_ID),
                                                      xmlelement("branch",
                                                                 dest.AC_BRANCH),
                                                      xmlelement("account",
                                                                 dest.AC_NO),
                                                      xmlelement("currency_code",
                                                                 dest.LCY),
                                                      xmlelement("account_name",
                                                                 dest.ACCOUNT_NAME),
                                                      xmlelement("client_number",
                                                                 dest.CUST_No),
                                                      xmlelement("personal_account_type",
                                                                 dest.ACCOUNT_TYPE),
            
                                                      xmlelement("signatory",
                                                                 xmlelement("is_primary",
                                                                            dest.is_primary),
                                                                 xmlelement("t_person",
                                                                            xmlelement("gender",
                                                                                       dest.GENDER),
                                                                            xmlelement("title",
                                                                                       dest.TITLE),
                                                                            xmlelement("first_name",
                                                                                       dest.FIRST_NAME),
                                                                            xmlelement("middle_name",
                                                                                       dest.MIDDLE_NAME),
                                                                            xmlelement("last_name",
                                                                                       dest.LAST_NAME),
                                                                            xmlelement("birthdate",
                                                                                       to_char(dest.DATE_OF_BIRTH,
                                                                                               'YYYY-MM-DD') || 'T' ||
                                                                                       to_char(dest.DATE_OF_BIRTH,
                                                                                               'HH24:MI:SS')),
                                                                            xmlelement("nationality1",
                                                                                       dest.NATIONALITY),
                                                                            xmlelement("phones",
                                                                                       xmlelement("tph_contact_type",
                                                                                                  dest.CONTACT_TYPE),
                                                                                       xmlelement("tph_communication_type",
                                                                                                  dest.COMMUNICATION_TYPE),
                                                                                       xmlelement("tph_country_prefix",
                                                                                                  dest.COUNTRY_CODE),
                                                                                       xmlelement("tph_number",
                                                                                                  dest.PHONE_NUMBER)),
                                                                            xmlelement("addresses",
                                                                                       xmlelement("address_type",
                                                                                                  dest.ADDRESS_TYPE),
                                                                                       xmlelement("address",
                                                                                                  dest.ADDRESS),
                                                                                       xmlelement("city",
                                                                                                  dest.CITY)),
                                                                            xmlelement("occupation",
                                                                                       dest.OCCUPATION),
            
                                                                            xmlelement("t_person_identification",
            
                                                                                       xmlelement("type",
                                                                                                  dest.ID_TYPE),
                                                                                       xmlelement("number",
                                                                                                  dest.ID_NUMBER),
                                                                                       xmlelement("issue_country",
                                                                                                  dest.ISSUED_IN)))),
            
                                                      xmlelement("opened",
                                                                 to_char(dest.AC_OPEN_DATE,
                                                                         'YYYY-MM-DD') || 'T' ||
                                                                 to_char(dest.AC_OPEN_DATE,
                                                                         'HH24:MI:SS')),
                                                      xmlelement("balance",
                                                                 dest.ACY_BALANCE),
                                                      xmlelement("status_code",
                                                                 dest.ACCOUNT_STATUS),
                                                      xmlelement("beneficiary",
                                                                 dest.BENEF_DETAILS)
            
                                                      )
            
                                          ELSE
            
                                           xmlelement("to_person",
                                                      xmlelement("gender",
                                                                 dest.GENDER),
                                                      xmlelement("title",
                                                                 dest.TITLE),
                                                      xmlelement("first_name",
                                                                 dest.FIRST_NAME),
                                                      xmlelement("middle_name",
                                                                 dest.MIDDLE_NAME),
                                                      xmlelement("last_name",
                                                                 dest.LAST_NAME),
                                                      xmlelement("birthdate",
                                                                 to_char(dest.DATE_OF_BIRTH,
                                                                         'YYYY-MM-DD') || 'T' ||
                                                                 to_char(dest.DATE_OF_BIRTH,
                                                                         'HH24:MI:SS')),
                                                      xmlelement("nationality1",
                                                                 dest.NATIONALITY),
            
                                                      xmlelement("phones",
                                                                 xmlelement("tph_contact_type",
                                                                            dest.CONTACT_TYPE),
                                                                 xmlelement("tph_communication_type",
                                                                            dest.COMMUNICATION_TYPE),
                                                                 xmlelement("tph_country_prefix",
                                                                            dest.COUNTRY_CODE),
                                                                 xmlelement("tph_number",
                                                                            dest.PHONE_NUMBER)),
                                                      xmlelement("addresses",
                                                                 xmlelement("address_type",
                                                                            dest.ADDRESS_TYPE),
                                                                 xmlelement("address",
                                                                            dest.ADDRESS),
                                                                 xmlelement("city",
                                                                            dest.CITY)),
                                                      xmlelement("occupation",
                                                                 dest.OCCUPATION),
            
                                                      xmlelement("t_person_identification",
            
                                                                 xmlelement("type",
                                                                            dest.ID_TYPE),
                                                                 xmlelement("number",
                                                                            dest.ID_NUMBER),
                                                                 xmlelement("issue_country",
                                                                            dest.ISSUED_IN)))
            
                                        END,
                                        xmlelement("to_country",
                                                   dest.country_code))
            
                                 Else
            
                                 xmlelement("t_to", xmlelement("to_funds_code", dest.To_FUNDS_TYPE), xmlelement("to_foreign_currency", xmlelement("foreign_currency_code", dest.FCY), xmlelement("foreign_amount", dest.FCY_AMOUNT), xmlelement("foreign_exchange_rate", dest.EXCH_RATE)),
            
                                               CASE
                                                 WHEN trans.transaction_mode <>
                                                       'Cash_Transaction' Then
                                                  xmlelement("to_account",
                                                                xmlelement("institution_name",
                                                                              dest.INSTITUTION_NAME),
            
                                                                xmlelement("institution_code",
                                                                              dest.INSTITUTION_ID),
            
                                                                xmlelement("account",
                                                                              dest.AC_NO))
            
                                                 ELSE
            
                                                  xmlelement("to_person",
            
                                                                xmlelement("first_name",
                                                                              dest.FIRST_NAME),
            
                                                                xmlelement("last_name",
                                                                              dest.LAST_NAME))
                                               end,
            
                                 xmlelement("to_country", dest.country_code))
            
                                 END))
                                      INTO V_XML
                           from trans    trans,
                                dest   dest,
                                source source
                           where source.AC_ENTRY_SR_NO =     dest.AC_ENTRY_SR_NO
                            AND trans.AC_ENTRY_SR_NO =     source.AC_ENTRY_SR_NO
                            AND trans.ENTITY_REF =     dest.ENTITY_REF
                            AND trans.ENTITY_REF =     source.ENTITY_REF
                             AND trans.entity_ref = p_entity_ref
                               AND  source.AC_ENTRY_SR_NO in (select * from  table(MyList)); 
             
            Edited by: 965358 on Dec 18, 2012 1:35 AM

            Edited by: 965358 on Dec 18, 2012 1:38 AM
            • 3. Re: Remove <?xml version = '1.0'?>
              968361
              rest of code is
                 v_blob := v_xml.getblobval (1);
                 v_data_length := DBMS_LOB.getlength (v_blob);
               
                 -- Open the file
                 fh := UTL_FILE.fopen (p_filepath, 'myxml1.xml', 'ab', v_chunk);
                 v_offset := 1;
                 -- Da loop de loop
                 LOOP
                    -- Exit when our file offset is bigger than our file
                    EXIT WHEN v_offset > v_data_length;
              
                    -- Write the output chunk by chunk
                           
                    UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
                                      TRUE);
              
                    -- Increment the offset by the amount written
                    v_offset := v_offset + v_chunk;
                 END LOOP;
                 
                UTL_FILE.fclose (fh);
              end loop;
              EXCEPTION
                 WHEN NO_DATA_FOUND
                 THEN
                    -- We won't write any data, or even open the file,
                    -- if the query return no rows
                  
              END;
              I tried using Clob the Prolog log is remioved but the xml becomes invalid. It would be great if you could suggeset any way by which i can remove Prolog from my xml

              Regards
              -Vinod

              Edited by: 965358 on Dec 18, 2012 1:36 AM

              Edited by: 965358 on Dec 18, 2012 1:38 AM
              • 4. Re: Remove <?xml version = '1.0'?>
                odie_63
                I tried using Clob the Prolog log is remioved but the xml becomes invalid. It would be great if you could suggeset any way by which i can remove Prolog from my xml
                The XML is invalid because there's no root element.
                The getblobval() call is generating the prolog, but as said don't generate it in the first place.

                My suggestion would be : do not use loops, collections or utl_file.
                Oracle is very capable of generating large XML contents so you don't have to process it in chunks.

                I'd do it like this :
                CREATE OR REPLACE PROCEDURE test1(
                  p_entity_ref IN VARCHAR2,
                  p_filepath IN VARCHAR2,
                  p_filename IN VARCHAR2
                ) 
                IS
                
                  v_xml      CLOB;
                  
                BEGIN
                
                  SELECT XMLElement("transactions",  --< add a root element here
                           XMLAgg(
                             XMLElement("transaction",
                             /* rest of the big query here */
                             )
                             order by trans.AC_ENTRY_SR_NO --< if necessary
                           )
                         ).getClobVal()
                  INTO v_xml
                  FROM trans    trans,
                       dest   dest,
                       source source
                  WHERE source.AC_ENTRY_SR_NO = dest.AC_ENTRY_SR_NO
                  AND trans.AC_ENTRY_SR_NO = source.AC_ENTRY_SR_NO
                  AND trans.ENTITY_REF = dest.ENTITY_REF
                  AND trans.ENTITY_REF = source.ENTITY_REF
                  AND trans.entity_ref = p_entity_ref ;
                
                  -- write the file in one operation : 
                  DBMS_XSLPROCESSOR.clob2file(v_xml, p_filepath, p_filename);
                  
                END;
                /