2 Replies Latest reply: May 4, 2013 11:22 AM by odie_63 RSS

    repeatable blocks with range in oracle

    1007098
      Please find the below table. The entire data is within DOCSTART and DOCEND. The data is further enclosed within BACCSTART and BACCEND. This type of block is repeatable. I have to pick up any of ABCD which is also repeatable and TOTAL(occurring once per block) and ACCNAME (occurring once per block) for each block within BACCSTART and BACCEND and form an xml like

      <BACCSTART>
      <TOTAL>100</TOTAL>
      <ABCD>abcd</ABCD>
      <ACCNAME>name</ACCNAME>
      </BACCSTART>

      for each such block. Presently I am using a for loop, but the performance is not up to the mark. It will have around 200 such blocks for which I have to form the xmls within 15 seconds. Presently the for loop is taking around 53 secs.

      ROWNUM   NAME     VALUE
      1 DOCSTART null
      2 BACCSTART null
      3 ABCD abcd
      4 ABCD abcd2
      5 PQRS pqrs
      6 PQRS pqrs2
      7 TOTAL 100
      8 ACCNAME name
      9 BACCEND null
      10 BACCSTART null
      11 ABCD abcd
      12 ABCD abcd2
      13 PQRS pqrs3
      14 PQRS pqrs4
      15 TOTAL 150
      16 ACCNAME name
      17 BACCEND null
      18 DOCEND null

      Please help me out with this.
        • 1. Re: repeatable blocks with range in oracle
          Solomon Yakobson
          Looks like plain XML file. Use XMLTABLE. Post example with few BACCSTART nodes.

          SY.
          • 2. Re: repeatable blocks with range in oracle
            odie_63
            1004095 wrote:
            The entire data is within DOCSTART and DOCEND. The data is further enclosed within BACCSTART and BACCEND. This type of block is repeatable. I have to pick up any of ABCD which is also repeatable and TOTAL(occurring once per block) and ACCNAME (occurring once per block) for each block within BACCSTART and BACCEND and form an xml like

            <BACCSTART>
            <TOTAL>100</TOTAL>
            <ABCD>abcd</ABCD>
            <ACCNAME>name</ACCNAME>
            </BACCSTART>
            Try the following query (using your table instead of the WITH clause).
            It first assigns a common group ID to each row of the same block, then groups those rows under a single BACCSTART node.
            SQL> with sample_data as (
              2    select 1 as rn, 'DOCSTART' as name, null as value from dual union all
              3    select 2, 'BACCSTART', null from dual union all
              4    select 3, 'ABCD', 'abcd' from dual union all
              5    select 4, 'ABCD', 'abcd2' from dual union all
              6    select 5, 'PQRS', 'pqrs' from dual union all
              7    select 6, 'PQRS', 'pqrs2' from dual union all
              8    select 7, 'TOTAL', '100' from dual union all
              9    select 8, 'ACCNAME', 'name' from dual union all
             10    select 9, 'BACCEND', null from dual union all
             11    select 10, 'BACCSTART', null from dual union all
             12    select 11, 'ABCD', 'abcd' from dual union all
             13    select 12, 'ABCD', 'abcd2' from dual union all
             14    select 13, 'PQRS', 'pqrs3' from dual union all
             15    select 14, 'PQRS', 'pqrs4' from dual union all
             16    select 15, 'TOTAL', '150' from dual union all
             17    select 16, 'ACCNAME', 'name' from dual union all
             18    select 17, 'BACCEND', null from dual union all
             19    select 18, 'DOCEND', null from dual
             20  )
             21  select xmlelement("BACCSTART"
             22         , xmlagg(
             23             case when v.name in ('ABCD','TOTAL','ACCNAME')
             24                  then xmlelement(evalname(v.name), v.value)
             25             end
             26           )
             27         ) as result_block
             28  from (
             29    select name
             30         , value
             31         , last_value(
             32             case when name = 'BACCSTART' then rn end
             33             ignore nulls
             34           ) over( order by rn ) as gid
             35    from sample_data
             36  ) v
             37  where gid is not null
             38  group by gid ;
            
            RESULT_BLOCK
            ---------------------------------------------------------------------------------------------------
            <BACCSTART><ABCD>abcd</ABCD><ACCNAME>name</ACCNAME><TOTAL>100</TOTAL><ABCD>abcd2</ABCD></BACCSTART>
            <BACCSTART><ABCD>abcd</ABCD><ACCNAME>name</ACCNAME><TOTAL>150</TOTAL><ABCD>abcd2</ABCD></BACCSTART>