This discussion is archived
12 Replies Latest reply: May 11, 2012 2:57 PM by Jason_(A_Non) RSS

Formatting data to xml consumes more time

user1107506 Newbie
Currently Being Moderated
Hi,

Database ver : 11.2.0.2.0

I have a procedure that reads nearly 10,000 records from PL/SQL table using loop and formatted to XML using package soap_api . Add_parameter and is assigned to a clob variable which is working fine. The issue what i am having is it is taking more time for formatting which affects the performance. Is there any better way to do this to make it fast.

Any help would be highly appreciated.

Thanks

Ankith
  • 1. Re: Formatting data to xml consumes more time
    odie_63 Guru
    Currently Being Moderated
    Hi,

    You'll have to explain your requirement a little further.

    SOAP_API is not a standard built-in package, I don't know what it does.
    The issue what i am having is it is taking more time for formatting which affects the performance
    What are you calling "formatting" here?

    Most likely, using PL/SQL to loop through records and calling a function as many times is the problem.
    Why not use set-based operation to build the soap request in a single statement? Take a look at SQL/XML functions XMLElement, XMLAttributes, XMLAgg ...
  • 2. Re: Formatting data to xml consumes more time
    user1107506 Newbie
    Currently Being Moderated
    Hi,

    This is the sample data from table1, table 2, table 3. The data is read using For LOOP and appended to clob variable.
    In table 2, 3 it checks the value of col1 and col2 is the same.
    if col2 value is not the same as the previous and col1 is the same then it closes col2 and starts wtih new value of col2.
    if col1 value is not the same as the previous then it closes col1 and col2 and starts wtih new value of col1 and col2.

    Out put Format is given after the table data below.

    Table 1

    Col1 (Attribute) col2 col3

    in_global_supplier_id number-val     1001     
    in_global_text_suppliertype     text-val     WS     
    in_global_date_created      date-val     2012-01-03     
    in_global_date_salestarted     date-val     2012-01-15     
    in_global_text_active      text-val     A     


    Table 2
    Col1 COl2 Col3 (attribute) Col4 Col5

    col1_supplier      supplieritempreference     in_supplieritempreference_text_option      text-val     Y
    col1_supplier      supplieritempreference     in_supplieritemdiscount_text_option      text-val     Y
    col1_supplier      supplieritempreference     in_supplieritemspecialdiscount_text_option      text-val     N
    col1_supplierterms supplierauthorizedterms     in_suppliercreditperiod_text_option      number-val     60
    col1_supplierterms     supplierauthorizedterms     in_supplierpayment_text_option     text-val CHQ
    col1_supplierterms     supplierauthorizedterms     in_supplierbankcertified_text_option     null on-approval


    Table 3
    Col1 col2 col3 col4 col5 col6

    col1_supplier1 SupplierReq1     ratechangeRequest_boolean_supplier_1 boolean-val     false     null
    col1_supplier1 SupplierReq1     ratechangeRequest_number_times_1     number-val null     null
    col1_supplier1 SupplierReq1     ratechangeRequest_author_mgr_1     text-val     Y      null
    col1_supplier1 SupplierReq1     ratechangeRequest_manager_approved_amount_1     number-val null on-approval
    col1_supplier2 SupplierReq2     ratechangeRequest_supplierCommission_2     number-val     null on-approval
    col1_supplier2 SupplierReq2     ratechangeRequest_boolean_supplier_2 boolean-val     false     null



    Out put Format:

    <t1:global-instance>
    <attribute id="in_global_supplier_id">
    <number-val>1001</number-val>
    </attribute>
    <attribute id="in_global_text_suppliertype">
    <text-val>WS</text-val>
    </attribute>
    <attribute id="in_global_date_created">
    <date-val>2012-01-03</date-val>
    </attribute>
    <attribute id="in_global_date_salestarted">
    <date-val>2012-01-15</date-val>
    </attribute>
    <attribute id="in_global_text_active">
    <text-val>A</text-val>
    </attribute>
    --------------------------------------------------------------------- table 2 -- if col 5 is null then <unknown-val/>
    <col1 id="entity_supplier">
    <col2 id="supplieritempreference">
    <attribute id="in_supplieritempreference_text_option">
    <text-val>Y</text-val>
    </attribute>
    <attribute id="in_supplieritemdiscount_text_option">
    <text-val>Y</text-val>
    </attribute>
    <attribute id="in_supplieritemspecialdiscount_text_option">
    <text-val>N</text-val>
    </attribute>
    </col2>
    <col2 id="supplierauthorizedterms">
    <attribute id="in_suppliercreditperiod_text_option">
    <number-val>60</number-val>
    </attribute>
    <attribute id="in_supplierpayment_text_option">
    <text-val>CHQ</text-val>
    </attribute>
    <attribute id="in_supplierbankcertified_text_option" column6="on-approval"/>
    </col2>
    </col1>
    --------------------------------------------------- table 3
    <col1 id="col1_supplier1">
    <col2 id="SupplierReq1">
    <attribute id="ratechangeRequest_boolean_supplier_1">
    <boolean-val>false</boolean-val>
    </attribute>
    <attribute id="ratechangeRequest_number_times_1"> -- if col 5 and 6 is null then <unknown-val/>
    <unknown-val/>
    </attribute>
    <attribute id="ratechangeRequest_author_mgr_1">
    <text-val>Y</text-val>
    </attribute>
    <attribute id="ratechangeRequest_manager_approved_amount_1" column6="on-approval"/>
    </col2>
    </col1>
    <col1 id="col1_supplier2">
    <col2 id="SupplierReq2">
    <attribute id="ratechangeRequest_supplierCommission_2" column6="on-approval"/> -- if col 6 is on-approval
    <attribute id="ratechangeRequest_boolean_supplier_2">
    <boolean-val>false</boolean-val>
    </attribute>
    </col2>
    </col1>
    </global-instance>

    Thanks in advance

    Ankith
  • 3. Re: Formatting data to xml consumes more time
    936029 Newbie
    Currently Being Moderated
    Hi Ankith,

    I use the XML format to back up all table in our application each day. Database version is 8.1.7, when you are using 11 version you can use DBMS_XMLGEN.GETXML to replace XMLGEN.GETXML or similar function.

    This is a simple ways to create XML file format.

    1.
    Create table contains CLOB data type.

    CREATE TABLE TEXT_CLOB_TEMP
    (
    TABLE_BACKUP VARCHAR2(50),
    SEQ_NO INTEGER,
    TEXT_CLOB CLOB
    );

    2.
    Create Procedure to insert each table to be backup by sending vtext_sql (select * from table_to_be_backup), vtable_backup=> name table to be back up.

    CREATE OR REPLACE FUNCTION KEUANGAN.get_xml_clob (vtext_sql in varchar2 ,vtable_backup in varchar2) RETURN char IS
    xmlstr VARCHAR2(32767);
    line VARCHAR2(2000);
    result clob;
    i integer;
    BEGIN

    result:=xmlgen.getXml(vtext_sql , 0);

    insert into text_clob_temp(table_backup,seq_no,text_clob)
    values(vtable_backup,1,result);
    commit;

    return(null);
    END;

    3. create procedure to get each 32767 text in CLOB to text, by sending parameter vtable_backup table to be backup, voffset => beginning number to get each 32767 text

    CREATE OR REPLACE FUNCTION KEUANGAN.get_clob_text (vtable_backup in varchar2,voffset in integer ) RETURN char IS
    xmlstr VARCHAR2(32767);
    line VARCHAR2(2000);
    vtext_clob clob;
    i integer;

    BEGIN

    begin
    select text_clob
    into vtext_clob
    from text_clob_temp
    where table_backup=vtable_backup;
    exception when no_data_found then
    vtext_clob :=null;
    end;

    if vtext_clob is not null then
    xmlstr := dbms_lob.substr(vtext_clob,32767,voffset);
    end if;

    return(xmlstr);
    END;


    4. Calling form, I am used is develop form Oracle Developer 6, the PL/SQL are written below

    declare     
         vtable_backup     varchar2(50);
         vtext_clob     varchar2(32767);
         filename     varchar2(100);
         vtext_temp     varchar2(32767);
         vtext      varchar2(32767);
         k number:=1;
         out_file Text_IO.File_Type;
    begin     
         
         filename:=:global.vdir_exp||:list_table||to_char(:vdate1,'ddmmyy')||'_'||to_char(:vdate2,'ddmmyy')||'.xml';

         out_file := Text_IO.Fopen(filename, 'w');
         
         vtable_backup:=:list_table||to_char(:vdate1,'ddmmyy')||'_'||to_char(:vdate2,'ddmmyy')||'.xml';

         vtext:='select *
    from '||:list_table||' where trans_date>=to_date('''||to_char(:vdate1,'ddmmyyyy')||''',''ddmmyyyy'')
    and trans_date<=to_date('''||to_char(:vdate2,'ddmmyyyy')||''',''ddmmyyyy'')
    order by trans_date';
         
         
         
         
         vtext_temp:=get_xml_clob(vtext,vtable_backup);

         loop
              vtext_temp:=get_clob_text(vtable_backup,k);
              exit when vtext_temp is null;
                             Text_IO.Put(out_file, vtext_temp);
                             
         
              k:=k+32767;
         end loop;     
         
         Text_IO.Fclose (out_file);      

                   
         delete
         from text_clob_temp;
         commit;

    end;     


    approximately 20-3- sec when backup 15000-17000 records.../table.
  • 4. Re: Formatting data to xml consumes more time
    odie_63 Guru
    Currently Being Moderated
    OK, thanks for explaining with sample data.

    I may have missed some rules but here's how I would do it.
    The idea is to maximize SQL usage to benefit from set-based optimizations and data retrieval.
    create table Table1 (
      col1 varchar2(80)
    , col2 varchar2(80)
    , col3 varchar2(80)
    );
    
    create table Table2 (
      col1 varchar2(80)
    , col2 varchar2(80)
    , col3 varchar2(80)
    , col4 varchar2(80)
    , col5 varchar2(80)
    );
    
    create table Table3 (
      col1 varchar2(80)
    , col2 varchar2(80)
    , col3 varchar2(80)
    , col4 varchar2(80)
    , col5 varchar2(80)
    , col6 varchar2(80)
    );
    
    insert into table1 values( 'in_global_supplier_id',       'number-val', '1001' );
    insert into table1 values( 'in_global_text_suppliertype', 'text-val',   'WS' );
    insert into table1 values( 'in_global_date_created',      'date-val',   '2012-01-03' );
    insert into table1 values( 'in_global_date_salestarted',  'date-val',   '2012-01-15' );
    insert into table1 values( 'in_global_text_active',       'text-val',   'A' );
    
    insert into table2 values( 'entity_supplier',      'supplieritempreference',  'in_supplieritempreference_text_option',      'text-val',   'Y' );
    insert into table2 values( 'entity_supplier',      'supplieritempreference',  'in_supplieritemdiscount_text_option',        'text-val',   'Y' );
    insert into table2 values( 'entity_supplier',      'supplieritempreference',  'in_supplieritemspecialdiscount_text_option', 'text-val',   'N' );
    insert into table2 values( 'entity_supplier', 'supplierauthorizedterms', 'in_suppliercreditperiod_text_option',        'number-val', '60' );
    insert into table2 values( 'entity_supplier', 'supplierauthorizedterms', 'in_supplierpayment_text_option',             'text-val',   'CHQ' );
    insert into table2 values( 'entity_supplier', 'supplierauthorizedterms', 'in_supplierbankcertified_text_option',       null,         'on-approval' );
    
    insert into table3 values( 'col1_supplier1', 'SupplierReq1', 'ratechangeRequest_boolean_supplier_1',        'boolean-val', 'false', null );
    insert into table3 values( 'col1_supplier1', 'SupplierReq1', 'ratechangeRequest_number_times_1',            'number-val',  null,    null );
    insert into table3 values( 'col1_supplier1', 'SupplierReq1', 'ratechangeRequest_author_mgr_1',              'text-val',    'Y',     null );
    insert into table3 values( 'col1_supplier1', 'SupplierReq1', 'ratechangeRequest_manager_approved_amount_1', 'number-val',  null,    'on-approval' );
    insert into table3 values( 'col1_supplier2', 'SupplierReq2', 'ratechangeRequest_supplierCommission_2',      'number-val',  null,    'on-approval' );
    insert into table3 values( 'col1_supplier2', 'SupplierReq2', 'ratechangeRequest_boolean_supplier_2',        'boolean-val', 'false', null );
    with all_data as (
      -- data from table1 : 
      select 1 as numset
           , xmlagg(
               xmlelement("attribute",
                 xmlattributes(col1 as "id")
               , xmlelement(evalname(col2), col3)
               )
             ) as xmlset
      from table1
      union all
      -- data from table2 : 
      select 2
           , xmlagg(
               xmlelement("col1",
                 xmlattributes(col1 as "id")
               , xmlagg(col2)
               )
             )
      from (
        select col1
             , xmlelement("col2",
                  xmlattributes(col2 as "id")
                , xmlagg(
                    xmlelement("attribute",
                      xmlattributes(
                        col3 as "id"
                      , nvl2(col4, null, col5) as "column6"
                      )
                    , case when col4 is not null then xmlelement(evalname(col4), col5) end
                    )
                  )
                ) as col2
        from table2
        group by col1, col2
      )
      group by col1
      union all
      -- data from table3 : 
      select 3
           , xmlagg(
               xmlelement("col1",
                 xmlattributes(col1 as "id")
               , xmlagg(col2)
               )
             )
      from (
        select col1
             , xmlelement("col2",
                  xmlattributes(col2 as "id")
                , xmlagg(
                    xmlelement("attribute",
                      xmlattributes(
                        col3 as "id"
                      , col6 as "column6"
                      )
                    , case when col5 is null and col6 is null then xmlelement("unknown-val")
                           when col6 is null then xmlelement(evalname(col4), col5)
                      end
                    )
                  )
                ) as col2
        from table3
        group by col1, col2
      )
      group by col1
    )
    select xmlserialize(document
             xmlelement("global-instance",
               xmlagg(xmlset order by numset)
             )
             as clob indent
           ) as result
    from all_data
    ;
    
    RESULT
    --------------------------------------------------------------------------------
    <global-instance>
      <attribute id="in_global_supplier_id">
        <number-val>1001</number-val>
      </attribute>
      <attribute id="in_global_text_suppliertype">
        <text-val>WS</text-val>
      </attribute>
      <attribute id="in_global_date_created">
        <date-val>2012-01-03</date-val>
      </attribute>
      <attribute id="in_global_date_salestarted">
        <date-val>2012-01-15</date-val>
      </attribute>
      <attribute id="in_global_text_active">
        <text-val>A</text-val>
      </attribute>
      <col1 id="entity_supplier">
        <col2 id="supplieritempreference">
          <attribute id="in_supplieritempreference_text_option">
            <text-val>Y</text-val>
          </attribute>
          <attribute id="in_supplieritemspecialdiscount_text_option">
            <text-val>N</text-val>
          </attribute>
          <attribute id="in_supplieritemdiscount_text_option">
            <text-val>Y</text-val>
          </attribute>
        </col2>
        <col2 id="supplierauthorizedterms">
          <attribute id="in_suppliercreditperiod_text_option">
            <number-val>60</number-val>
          </attribute>
          <attribute id="in_supplierbankcertified_text_option" column6="on-approval"/>
          <attribute id="in_supplierpayment_text_option">
            <text-val>CHQ</text-val>
          </attribute>
        </col2>
      </col1>
      <col1 id="col1_supplier1">
        <col2 id="SupplierReq1">
          <attribute id="ratechangeRequest_boolean_supplier_1">
            <boolean-val>false</boolean-val>
          </attribute>
          <attribute id="ratechangeRequest_manager_approved_amount_1" column6="on-approval"/>
          <attribute id="ratechangeRequest_author_mgr_1">
            <text-val>Y</text-val>
          </attribute>
          <attribute id="ratechangeRequest_number_times_1">
            <unknown-val/>
          </attribute>
        </col2>
      </col1>
      <col1 id="col1_supplier2">
        <col2 id="SupplierReq2">
          <attribute id="ratechangeRequest_supplierCommission_2" column6="on-approval"/>
          <attribute id="ratechangeRequest_boolean_supplier_2">
            <boolean-val>false</boolean-val>
          </attribute>
        </col2>
      </col1>
    </global-instance>
  • 5. Re: Formatting data to xml consumes more time
    user1107506 Newbie
    Currently Being Moderated
    Hi

    Thanks for the help. This works fine.
    I need one more help from you. How do i add namespace 't1:' .

    eg: <t1:global-instance>
    <t1:attribute id='"xxxxxxxxxxxxx">
    <t1:number-val></t1:number-val>
    </t1:attribute>
    </t1:global-instance>

    Thanks once again for the help

    Ankith
  • 6. Re: Formatting data to xml consumes more time
    odie_63 Guru
    Currently Being Moderated
    Declare the prefix in the root element (with xmlattributes function) and add it in front of each element name :
    xmlelement("t1:global-instance",
      xmlattributes('some.namespace.uri' as "xmlns:t1"),
      ...
    )
  • 7. Re: Formatting data to xml consumes more time
    Jason_(A_Non) Expert
    Currently Being Moderated
    I'll expand upon the answer Marc gave as I have it now that I gave up trying to figure out why my GUI tool was not returning results from my test queries yet SQL*Plus was.
    select xmlelement("t:global-instance",
                      XMLAttributes('http://your.namespace.here' AS
                                      "xmlns:t"),
                      XMLElement("t:child1",'c1'),
                      XMLForest('c2' AS "t:child2",
                                'c3' AS "t:child3"),
                      XMLElement(evalname('t1:' || dummy), 'c4')) rslt 
    from dual;
    It shows what Marc mentioned plus how to handle the evalname if you need the namespace prefix there too.
  • 8. Re: Formatting data to xml consumes more time
    user1107506 Newbie
    Currently Being Moderated
    Hi odie,

    Thanks for the help.

    Can you just show me in the select statement with all_data as
    where exactly to use this namespace (t1:)

    thanks once again

    Ankith
  • 9. Re: Formatting data to xml consumes more time
    odie_63 Guru
    Currently Being Moderated
    Honestly, I think it would be in your best interest to try to understand how it works and do it yourself.

    All you have to do is adding the prefix for every element name.
    See Jason's (A_Non) post above for an additional example.

    The documentation might help too :
    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB4994
  • 10. Re: Formatting data to xml consumes more time
    user1107506 Newbie
    Currently Being Moderated
    Hi

    Thanks for all the help. I will give a try.

    Thanks once again

    Ankith
  • 11. Re: Formatting data to xml consumes more time
    user1107506 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the help.

    I was able to fix t1: issues. when i run the script

    the soap:header is formatted as
    <soapenv:Header/>

    should be
    <soapenv:header> </soapenv:header>

    How do i fix this.

    Thanks

    Ankith

    select xmlserialize(document
    xmlelement("soapenv:Envelope",
    XMLAttributes('http://my.com' AS "xmlns:q0",
    'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soapenv",
    'http://www.myc.com/2012/XMLSchema' AS "xmlns:xsd",
    'http://www.my.com/2012/XMLSchema-instance' AS "xmlns:xsi"),
    xmlelement("soapenv:Header"),
    xmlelement("soapenv:body",
    xmlelement("t1:assess-request",
    xmlelement("t1:global-instance",
    xmlagg(xmlset order by numset)

    )
    )
    )
    )
    as clob indent
    ) as result
    from all_data
    --------------------------

    <soapenv:Envelope xmlns:q0="http://my.com"
    xmlns:soapenv="http://schemas.xmlsoap.com/soap/envelope/"
    xmlns:xsd="http://www.my.com/2012/XMLSchema"
    xmlns:xsi="http://www.my.com/2012/XMLSchema-instance">
    <soapenv:Header/> --************************************************ should be <soapenv:header> </soapenv:header>
    <soapenv:body>
    <t1:assess-request>
    <t1:global-instance>
    <t1:attribute id="supplier_id">
    <number-val>1001</number-val>
    </t1:attribute>
    </t1:global-instance>
    </t1:assess-request>
    </soapenv:body>
    </soapenv:Envelope>
  • 12. Re: Formatting data to xml consumes more time
    Jason_(A_Non) Expert
    Currently Being Moderated
    You have no data to be included within your soapenv:Header so Oracle created the empty tag.
    Both
    <soapenv:Header/>
    and
    <soapenv:header></soapenv:header>
    are semantically equivalent, meaning the receiver treats them the same.

    If you meant for spaces to be put in as your post above shows, then you need to add them as the second parm on XMLElement.

Legend

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