Home » Developer & Programmer » JDeveloper, Java & XML » How to Generate XML Output From Oracle Database Using A Procedure (Oracle 11g, 11.2.0.3.0,Windows)
How to Generate XML Output From Oracle Database Using A Procedure [message #636558] Tue, 28 April 2015 03:11 Go to next message
oraQ
Messages: 57
Registered: January 2011
Member
Hi,

I need to export table data in xml format from Oracle. Can anybody give any suggestion on how to accomplish this through a Procedure? Lets say I have two tables AA and AB with cols a, b, c in AA and d, e, f in AB and I need to have xml formatted output taking the data of a, b, d and f cols.

AA     AB

a b c  d e f


Thanks for any suggestion.
Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636560 is a reply to message #636558] Tue, 28 April 2015 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What should be the result?

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636561 is a reply to message #636560] Tue, 28 April 2015 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The simplest way is something like:
SQL> select dbms_xmlgen.getXMLtype('select * from emp where rownum<=3')
  2  from dual;
DBMS_XMLGEN.GETXMLTYPE('SELECT*FROMEMPWHEREROWNUM<=3')
-------------------------------------------------------------------------
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17/12/1980 00:00:00</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20/02/1981 00:00:00</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/02/1981 00:00:00</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
</ROWSET>

1 row selected.

[Updated on: Tue, 28 April 2015 03:35]

Report message to a moderator

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636562 is a reply to message #636561] Tue, 28 April 2015 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or:
SQL> select xmlserialize(document
  2           xmlelement("Employee",
  3                      xmlforest(empno,ename,job,sal,comm,hiredate,deptno))
  4           as clob indent size=2)
  5  from emp
  6  where rownum <= 3;
XMLSERIALIZE(DOCUMENTXMLELEMENT("EMPLOYEE",XMLFOREST(EMPNO,ENAME,JOB,SAL,COMM,HI
--------------------------------------------------------------------------------
<Employee>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <SAL>800</SAL>
  <HIREDATE>1980-12-17</HIREDATE>
  <DEPTNO>20</DEPTNO>
</Employee>
<Employee>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <HIREDATE>1981-02-20</HIREDATE>
  <DEPTNO>30</DEPTNO>
</Employee>
<Employee>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <HIREDATE>1981-02-22</HIREDATE>
  <DEPTNO>30</DEPTNO>
</Employee>

3 rows selected.

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636563 is a reply to message #636562] Tue, 28 April 2015 04:39 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Thanks for quick response Michel.

But can you please suggest on how to get the xml formatted output by using an Oracle Procedure?

Appreciate your suggestion and guide as always.
Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636565 is a reply to message #636563] Tue, 28 April 2015 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you want to do it in a procedure when you can directly do it in SQL?
Just return the string the SQL generates.

Edit: or in the first statement, just return the xml the procedure returns.

[Updated on: Tue, 28 April 2015 05:31]

Report message to a moderator

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636569 is a reply to message #636565] Tue, 28 April 2015 07:09 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Yeah Michel, we can have the xml formatted output through the technique as you mentioned, but I am keen to generate the same formatted output but through a Oracle Procedure. May be, it could help in providing the same advantages as in case of Oracle procedures.
Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636571 is a reply to message #636569] Tue, 28 April 2015 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what is your problem in writing this procedure from what I posted?

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636604 is a reply to message #636571] Wed, 29 April 2015 01:13 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Yes the single line query as you mentioned here,gives the output but from one table only. What I am trying is to get the same formatted output from multiple tables using a procedure(or the xml related functions). Could you please provide any suggestion for that?
Thanks for your time.
Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636605 is a reply to message #636604] Wed, 29 April 2015 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, it gives the result for any query you can write.

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636836 is a reply to message #636605] Mon, 04 May 2015 04:33 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Hi Michel,
I am still unclear on how to get the XML formatted output through an Oracle Procedure. Could you please provide some hints on how to develop that one to get the required output?

Thanks for your guidance.
Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636837 is a reply to message #636836] Mon, 04 May 2015 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I am still unclear on what is your problem in writing the procedure. Can you please provide what you have tried?

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636839 is a reply to message #636837] Mon, 04 May 2015 05:15 Go to previous messageGo to next message
oraQ
Messages: 57
Registered: January 2011
Member
Hi Michel,

Suppose I am using the traditional emp and dept tables to retrieve all the columns matching, then I have to query as follows:

Quote:
select empno,ename,job,dname,loc
from emp e,dept d
where e.deptno=d.deptno;

Now, I can get the o/p as follows:

EMPNO	ENAME	JOB	DNAME	LOC
7782	CLARK	MANAGER	ACCOUNTING	NEW YORK
7934	MILLER	CLERK	ACCOUNTING	NEW YORK
7839	KING	PRESIDENT ACCOUNTING    NEW YORK
7902	FORD	ANALYST	RESEARCH	DALLAS
7788	SCOTT	ANALYST	RESEARCH	DALLAS
....     ....   ....... ........        ......

So, now if I need the above data in xml format then what I have to do please suggest.

Re: How to Generate XML Output From Oracle Database Using A Procedure [message #636840 is a reply to message #636839] Mon, 04 May 2015 05:19 Go to previous message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read what I've shown you above.

[Updated on: Mon, 04 May 2015 05:20]

Report message to a moderator

Previous Topic: Getting error while using ExtractValue function.
Next Topic: adf internet publishing
Goto Forum:
  


Current Time: Wed Sep 30 01:11:48 CDT 2020