Generate XML from a table

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Generate XML from a table

Postby Sampath » Fri Mar 30, 2012 3:36 pm

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.
Sampath
Member
 
Posts: 39
Joined: Wed Aug 12, 2009 3:40 pm

Re: Generate XML from a table

Postby Tim... » Fri Mar 30, 2012 3:46 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 7 guests