OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » XML file generation (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XML file generation [message #663574]

Thu, 08 June 2017 12:36

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.

Report message to a moderator

Re: XML file generation [message #663575 is a reply to message #663574]

Thu, 08 June 2017 14:18

Michel Cadot
Messages: 67650
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>

Report message to a moderator

Previous Topic:	Converting XML Data to Rows.
Next Topic:	unload the data from the Oracle database into xml, using java.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 23 15:40:13 CST 2021