This forum is currently locked. You can't register or post questions at this time. (read more)

Generate XML from a table

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Sampath
Member
Posts: 39
Joined: Wed Aug 12, 2009 3:40 pm

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron