XML output from SQL query

To generate XML output SQL query, we can simply use dbms_xmlgen.getxml, for example

SQL> select dbms_xmlgen.getxml('select empno, ename, dname from scott.emp e, scott.dept d where d.deptno=e.deptno') from dual;

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7782</EMPNO>
  <ENAME>CLARK</ENAME>
  <DNAME>ACCOUNTING</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <DNAME>ACCOUNTING</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <DNAME>ACCOUNTING</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7566</EMPNO>
  <ENAME>JONES</ENAME>
  <DNAME>RESEARCH</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</ENAME>
  <DNAME>RESEARCH</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <DNAME>RESEARCH</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <DNAME>RESEARCH</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7788</EMPNO>
  <ENAME>SCOTT</ENAME>
  <DNAME>RESEARCH</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <DNAME>SALES</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7844</EMPNO>
  <ENAME>TURNER</ENAME>
  <DNAME>SALES</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <DNAME>SALES</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <DNAME>SALES</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</ENAME>
  <DNAME>SALES</DNAME>
 </ROW>
 <ROW>
  <EMPNO>7654</EMPNO>
  <ENAME>MARTIN</ENAME>
  <DNAME>SALES</DNAME>
 </ROW>
</ROWSET>

Related Posts:
- Generate XML data using SQL in Oracle Database
- Create CSV file using PL/SQL
- Load CSV file in Oracle using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Load XML File in Oracle Table

3 comments:

  1. thanks buddy but i hve one question what's the difference between the 2 pakcages dbms_xmlgen and dbms_xquery

    ReplyDelete
  2. Cool! However I notice the function converts dates to the Oracle DD-MON-YY format. XML ideally should convert date fields to the YYYY-MM-DDTHH24:MI:SS format. Is there a way to control this behaviour?

    ReplyDelete
  3. The workaround may be is:
    select dbms_xmlgen.getxml('select empno, ename, dname, to_char(HIREDATE,''YYYY-MM-DD
    HH24:MI:SS'') HIREDATE from scott.emp e, scott.dept d where d.deptno=e.deptno') from dual

    ReplyDelete