| How to Generate XML Output From Oracle Database Using A Procedure [message #636558] |
Tue, 28 April 2015 03:11  |
 |
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.
Thanks for any suggestion.
|
|
|
|
|
|
| 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  |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #636839 is a reply to message #636837] |
Mon, 04 May 2015 05:15 |
|
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
.... .... ....... ........ ......
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
