Home » Developer & Programmer » JDeveloper, Java & XML » table output to XML possible? (oracle 10g)
table output to XML possible? [message #486389] Wed, 15 December 2010 05:32 Go to next message
anand_gp
Messages: 18
Registered: November 2010
Location: Bangalore
Junior Member
I am exploring the possiblities of getting table output or query output to an XML file. Please let me know whether it is possible in Oracle. (a similary way like external table option).
Thanks in advance.
Re: table output to XML possible? [message #486392 is a reply to message #486389] Wed, 15 December 2010 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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>

Regards
Michel
Re: Is table output to XML possible [message #486486 is a reply to message #486392] Thu, 16 December 2010 00:41 Go to previous messageGo to next message
anand_gp
Messages: 18
Registered: November 2010
Location: Bangalore
Junior Member
Thanks for the reply.
But that did not work, it was giving error.

I used following -

select dbms_xmlquery.getxml('select id,full_date,day_name from dimension_time where rownum<=3') from dual

Error:
ORA-00904: "DBMS_XMLQUERY"."GETXML": invalid identifier

Then I used following -

select dbms_xmlgen.getxml('select id,full_date,day_name from dimension_time where rownum<=3') from dual

And it executed successfully.

Best Regards,
-Anand
Re: Is table output to XML possible [message #486497 is a reply to message #486486] Thu, 16 December 2010 01:22 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if you have this package in DBA_OBJECTS, if not then search in $ORACLE_HOME/rdbms/admin which script creates it and execute it.
Else check if there is the PUBLIC SYNONYM, if not then create it.

Regards
Michel
Previous Topic: read xml parameter (merged)
Next Topic: xml file from an xsd file?
Goto Forum:
  


Current Time: Thu Mar 28 12:35:57 CDT 2024