Home » SQL & PL/SQL » SQL & PL/SQL » Generate XML from PL/SQL proc (SQL, PL/SQL)
Generate XML from PL/SQL proc [message #598629] Wed, 16 October 2013 06:34 Go to next message
plsqlish
Messages: 8
Registered: December 2011
Junior Member
HI,

I have a requirement to generate the XML file from a pl/sql procedure.
The methods which I have searched are either generating the xml from a single query or they are explicitely adding the nodes and elements.
I need the method where I can dynamically append child nodes to the file.

Thanks,

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598633 is a reply to message #598629] Wed, 16 October 2013 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You may just use SQL and built-in features or packages for this like:
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <EMPNO>7369</EMPNO>
      <ENAME>SMITH</ENAME>
      <JOB>CLERK</JOB>
      <MGR>7902</MGR>
      <HIREDATE>12/17/1980 0:0:0</HIREDATE>
      <SAL>800</SAL>
      <DEPTNO>20</DEPTNO>
   </ROW>
   <ROW num="2">
      <EMPNO>7499</EMPNO>
      <ENAME>ALLEN</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/20/1981 0:0:0</HIREDATE>
      <SAL>1600</SAL>
      <COMM>300</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
   <ROW num="3">
      <EMPNO>7521</EMPNO>
      <ENAME>WARD</ENAME>
      <JOB>SALESMAN</JOB>
      <MGR>7698</MGR>
      <HIREDATE>2/22/1981 0:0:0</HIREDATE>
      <SAL>1250</SAL>
      <COMM>500</COMM>
      <DEPTNO>30</DEPTNO>
   </ROW>
</ROWSET>

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598685 is a reply to message #598633] Wed, 16 October 2013 15:10 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
In my database I don't have dbms_xmlquery.getxml package.
how to download it?
Please tell me how to configure this package.....


Thanks & Regards,
The Learner.

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598686 is a reply to message #598685] Wed, 16 October 2013 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post complete results from SQL below

SELECT * FROM V$VERSION;


http://www.oracle.com/pls/db121/search?remark=quick_search&word=dbms_xmlquery

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598687 is a reply to message #598685] Wed, 16 October 2013 15:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
thelearner wrote on Thu, 17 October 2013 01:40
In my database I don't have dbms_xmlquery.getxml package.


It was introduced in Oracle 8i as a Java Servlet. In Oracle 9i its functionality has been extended, with a subset of it's functionality being incorporated into the database kernel giving improved performance along with Java and PL/SQL APIs.

How did you conclude it is not there? What is your DB version?

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598688 is a reply to message #598686] Wed, 16 October 2013 15:17 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598689 is a reply to message #598688] Wed, 16 October 2013 15:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Look at this DBMS_XMLQUERY in Oracle 10g

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598700 is a reply to message #598686] Wed, 16 October 2013 16:48 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
I used dbms_xmlgen package in scott account....
when I executed the same statement in scott schema (in toad) it showing...
select dbms_xmlgen.getxml('select * from emp where rownum <= 3') from dual;


<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20-FEB-81</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>22-FEB-81</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
</ROWSET>


but in sql command line it is showing some part of the xml only...
SQL> conn scott/tiger
Connected.
SQL> select dbms_xmlgen.getxml('select * from emp where rownum <= 3') from dual;


DBMS_XMLGEN.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAM


SQL>

how to get complete XML report...

Thanks
The Learner

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598710 is a reply to message #598700] Wed, 16 October 2013 18:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
thelearner wrote on Wed, 16 October 2013 17:48
but in sql command line it is showing some part of the xml only...


Discover SQL*Plus commands

SET LONG

and

SET LONGCHUNKSIZE

SY.

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598759 is a reply to message #598710] Thu, 17 October 2013 08:59 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
Thank you Solomon Yakobson.... I got the complete output using SET LONG and SET LONGCHUNKSIZE
SQL> select dbms_xmlgen.getxml('select * from emp where rownum <= 3') from dual;


DBMS_XMLGEN.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>

DBMS_XMLGEN.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------

 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20-FEB-81</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>

DBMS_XMLGEN.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------

  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>22-FEB-81</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
</ROWSET>


SQL>



Thanks
The Learner.

[Updated on: Thu, 17 October 2013 09:00]

Report message to a moderator

Re: Generate XML from PL/SQL proc [message #598761 is a reply to message #598759] Thu, 17 October 2013 09:05 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And with set pagesize 10000 you will avoid the multiple heading lines (0 to remove them completely).

Report message to a moderator

Previous Topic: /*+ ALL_ROWS */
Next Topic: Help with PL SQL
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 14:43:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.