elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


 Producing XML data from relational data

Increasingly users want to know how to get data out of their Oracle databases into an XML type format. Thankfully Oracle has plenty of ways to do so. Here's an example based on the scott.emp table. This is what its contents
looks like based on a regular SELECT

SQL> set pages 0
SQL > select empno,ename,job
from scott.emp
/

7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK

14 rows selected.

​Now one of the ways to get this in XML format is to use the xmlelement and xmlforest constructs

SQL> set long 10000
SQL> select xmlelement("EMPREC",xmlforest(empno,ename,job))2
​ from scott.emp3
​/

<emprec>
​<empno>7396</empno>
<ename>
SMITH</ename>
<job>
CLERK</job>
</emprec>
<emprec>
<empno>7499</empno>
<ename>ALLEN</ename>
<job>SALESMAN</job>
</emprec>
etc ...



14 rows selected.



Example 2

----------




set pages 0
set long 32500
set lines 32500
set trimspool on
SET FEEDBACK OFF
SET VERIFY OFF
set termout off
column rec format a32500
alter session enable parallel dml
/
spool qtcompdaily.xml
select
   '<?xml version="1.0" encoding="UTF-8"?>' ||
'<QTCompositeIndex name="' || 'Default" ' || 'instrument="'
|| i.i_bloom || '" ' ||
'businessDate="' || to_char(i2.i_date,'YYYY-MM-DD') || '"><Info>' ||
xmlagg
   (
      xmlelement("Entry",xmlattributes
                         (
                        a.af_mnem as "key",
                         round(a.af_weight,4) as "value"
                         )
                )
   ).getclobval() || '</Info></QTCompositeIndex>' rec
from indx i, alloc_weights a, i_dhist i2
where i.i_bloom in
(
'HSFBE01U',
'HSFBEASU',
'HSFBEAWU',
'HSFBEBRU',
'HSFBEEAU',
'HSFBEEMU',
'HSFBELAU',
'HSFBEMEU'
)
and NVL(a.wgt_enddate,trunc(i2.i_date)) >= trunc(i2.i_date)
and trunc(a.wgt_startdate) <= trunc(i2.i_date)
and trunc(i2.i_date) = trunc(sysdate)
and i2.i_mnem = i.i_mnem
and af_family = i.i_mnem
and not exists (select 'x' from demised where p_or_i_mnem = i.i_mnem and trunc(sysdate) >= trunc(effective))
group by i.i_bloom,i2.i_date
union all
select
   '<?xml version="1.0" encoding="UTF-8"?>' ||
'<QTCompositeIndex name="' || 'Default" ' || 'instrument="'
|| 'HSFATC1U' || '" ' ||
'businessDate="' || to_char(i2.i_date,'YYYY-MM-DD') || '"><Info>' ||
xmlagg
   (
      xmlelement("Entry",xmlattributes
                         (
                         a.af_mnem as "key",
                         round(a.af_weight,4) as "value"
                         )
                )
   ).getclobval() || '</Info></QTCompositeIndex>' rec
from indx i, alloc_weights a, i_dhist i2
where a.af_family = 'HFATC1S'
and NVL(a.wgt_enddate,trunc(i2.i_date)) >= trunc(i2.i_date)
and trunc(a.wgt_startdate) <= trunc(i2.i_date)
and trunc(i2.i_date) = trunc(sysdate)
and i2.i_mnem = i.i_mnem
and af_family = i.i_mnem
and trunc(i2.i_date) not in
(select sp_date from spirit_hollu where calendar = 'LON')
and not exists (select 'x' from demised where p_or_i_mnem = i.i_mnem and trunc(sysdate) >= trunc(effective))
group by i.i_bloom,i2.i_date
/
spool off
exit