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
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.
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.
https://oracle-base.com/articles/mis ... ingSql.php
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 2 guests