2 Replies Latest reply: Mar 15, 2013 5:12 PM by odie_63 RSS

    grouping in xmlserialization

    spr47_pl/sql
      Hi all,

      how do i group values in xml serialization

      my query
      select xmlserialize(document
                   xmlelement("l",
                     xmlattributes(locale_code as "id", BASE_FLAG AS "b" , dbms_lob.substr(text) as "d")
                   )
                   as Nclob indent
                 ) as row_data
         from wrk_catalog_rd_l ;
      would return something like this

      1 <l id=0 b=0 d='abcd'/>
      1 <l id=0 b=0 d='abcd'/>
      2 <l id=1 b=1 d='abcd'/>

      but i need something like this..

      1 <l id=0 b=0 d='abcd'/> <l id=0 b=0 d='abcd'/>
      2 <l id=1 b=1 d='abcd'/>

      your help is appreciated... thank you...
        • 1. Re: grouping in xmlserialization
          ranit B
          Something like this?
          In Oracle 11g
          ranit@XE11GR2>> with xx as(
            2      select '1' id, '<l id=0 b=0 d=''abcd''/>' txt from dual UNION ALL
            3      select '1' id, '<l id=0 b=0 d=''abcd''/>' txt from dual UNION ALL
            4      select '2' id, '<l id=1 b=1 d=''abcd''/>' txt from dual
            5  )
            6  select
            7      id,
            8      LISTAGG(txt,' ') WITHIN group (order by id) lst
            9  from xx
           10  group by id;
          
          ID LST
          -- ---------------------------------------------
          1  <l id=0 b=0 d='abcd'/> <l id=0 b=0 d='abcd'/>
          2  <l id=1 b=1 d='abcd'/>
          In your case, try this Not tested
          WITH xx AS(
          select id, -- "added newly"
                 xmlserialize(document
                   xmlelement("l",
                     xmlattributes(locale_code as "id", BASE_FLAG AS "b" , dbms_lob.substr(text) as "d")
                   ) as Nclob indent
                 ) as row_data
             from wrk_catalog_rd_l
          )
          --
          -- "'XX' holds the output of the query you provided"
          --
          select 
              id,
              LISTAGG(row_data,' ') WITHIN group (order by id) row_dta
          from xx
          group by id;
          Also, can try XMLAgg - http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions233.htm
          • 2. Re: grouping in xmlserialization
            odie_63
            SQL> set long 5000
            SQL> 
            SQL> select deptno
              2       , xmlserialize(content
              3           xmlagg(
              4             xmlelement("emp",
              5               xmlattributes(empno as "id", ename as "name")
              6             )
              7             order by empno
              8           )
              9           as clob indent
             10         )
             11  from scott.emp
             12  group by deptno ;
             
            DEPTNO XMLSERIALIZE(CONTENTXMLAGG(XML
            ------ --------------------------------------------------------------------------------
                10 <emp id="7782" name="CLARK"/>
                   <emp id="7839" name="KING"/>
                   <emp id="7934" name="MILLER"/>
             
                20 <emp id="7369" name="SMITH"/>
                   <emp id="7566" name="JONES"/>
                   <emp id="7902" name="FORD"/>
             
                30 <emp id="7499" name="ALLEN"/>
                   <emp id="7521" name="WARD"/>
                   <emp id="7654" name="MARTIN"/>
                   <emp id="7698" name="BLAKE"/>
                   <emp id="7844" name="TURNER"/>
                   <emp id="7900" name="JAMES"/>
             
            Also note the change from the DOCUMENT keyword to CONTENT in XMLSerialize. It allows serializing XML fragments (not single-rooted).