Generate XML from a table

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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"?>
<userdetail userid="SCOTT" />
<cinfo ccode="C142" cname="Test Company1">
<val Eno="1" Ename="ABC" />
<val Eno="2" Ename="PQR" />

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.


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

Re: Generate XML from a table

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


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. ... ingSql.php


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:
My blog:

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest