Home » Developer & Programmer » JDeveloper, Java & XML » XML file generation (Oracle 11g)
XML file generation [message #663574] Thu, 08 June 2017 12:36 Go to next message
ORA2015
Messages: 49
Registered: March 2015
Member
Hello,

I have to submit a document in XML format. I was also given a schema to work with but not sure how to apply it.
Our data is stored in an oracle database and sql queries are usually done on PL/SQL developer to retrieve output in excel format.
However i need output in xml format. How do i do this?
I understand that i can include items like the below..but how do i get the output in xml and how do i integrate the schema provided.

SELECT XMLElement( "DEPARTMENT"
, department_name
)
FROM departments
WHERE department_id IN (10, 20);

Your assistance will be greatly appreciated.
Re: XML file generation [message #663575 is a reply to message #663574] Thu, 08 June 2017 14:18 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's of what you can do:
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>
Previous Topic: Converting XML Data to Rows.
Next Topic: unload the data from the Oracle database into xml, using java.
Goto Forum:
  


Current Time: Thu Mar 28 07:45:07 CDT 2024