This discussion is archived
2 Replies Latest reply: Mar 15, 2013 3:12 PM by odie_63 RSS

grouping in xmlserialization

spr47_pl/sql Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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).

Legend

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