dbms_xmlgen oracle get dynamic xmltype

Question

I have a table t in oracle:

select op_id, dimorder, title from t:

OP_ID|DIMORDER|TITLE
-----+--------+-----
  312|       1|AAA
  312|       2|BBB

I would like to achieve, with dbms_xmlgen.getXml this result (ONLY ONE ROW):

OP_ID|NEW_COLUMN
-----+----------
  312|<ROWSET><ROW><DIMORDER>1</DIMORDER><TITLE>AAA</TITLE>
       </ROW><ROW><DIMORDER>2</DIMORDER><TITLE>BBB</TITLE></ROW></ROWSET>

Thanks a lot in advance for your help

Answer 1

The SQL/XML generation functions are the preferred way of generating XML from relational data.. Assuming you have multiple set of op_id rows does something like this work..

 SQL>  set long 10000
 SQL> with MY_TABLE as
   2  (
   3     select 312 as op_id, 1 as dimorder, 'AAA' as title
   4        from dual
   5     union all
   6     select 312 as op_id, 2 as dimorder, 'BBB' as title
   7        from dual
   8     union all
   9     select 313 as op_id, 1 as dimorder, 'CCC' as title
  10        from dual
  11     union all
  12     select 313 as op_id, 2 as dimorder, 'DDD' as title
  13        from dual
  14  )
  15  select XMLELEMENT("ROWSET",
  16           XMLELEMENT("ROW",
  17             XMLAGG(
  18               XMLFOREST("DIMORDER" as DIMORDER, "TITLE" as TITLE)
  19               ORDER BY DIMORDER
  20             )
  21           )
  22         ) RESULT
  23    from MY_TABLE
  24   group by OP_ID
  25  /

 RESULT
 --------------------------------------------------------------------------------
 <ROWSET><ROW><DIMORDER>1</DIMORDER><TITLE>AAA</TITLE><DIMORDER>2</DIMORDER><TITLE>BBB</TITLE></ROW></ROWSET>

 <ROWSET><ROW><DIMORDER>1</DIMORDER><TITLE>CCC</TITLE><DIMORDER>2</DIMORDER><TITLE>DDD</TITLE></ROW></ROWSET>


 SQL>