Generate XML from a table

[Sampath]

Good Morning Tim,

Could you please help me in implementing the below XML.

create table emp
(eno number,ename varchar2(20),esal number,ccode varchar2(4));

insert into emp values(1,'ABC',7000,'CMP1');
insert into emp values(2,'PQR',8000,'CMP1');
insert into emp values(3,'XYZ',8000,'CMP2');

create table company
(ccode varchar2(4),
cname varchar2(20));

insert into company values('CMP1','Test Company1');
insert into company values('CMP2','Test Company2');
insert into company values('CMP3','Test Company3');

create table result_XML(ccode varchar2(4),result clob);

I need to implement a stored proc that accepts a ccode(for ex. 'CMP1') as parameter, generate the XML and store the result(in clob or xmltype column) in a table as below.

<?xml version="1.0" encoding="utf-8"?>
<Input>
<procdate>03/30/2012</procdate>
<userdetail userid="SCOTT" />
<cinfo ccode="C142" cname="Test Company1">
<val Eno="1" Ename="ABC" />
<val Eno="2" Ename="PQR" />
</cinfo>
</Input>


procdate displays SYSDATE and userid displays Database user.

Quick help is much appreciated as this is an immediate task to be completed.
Thanks in advance.

Regards,
Sampath.

[Tim...]

Hi.

There are many ways to generate XML from table data. You can concatenate the data together as a string manually, or you could use SQLXML, which I like.

http://www.oracle-base.com/articles/mis ... ingSql.php

Cheers

Tim...