Home » Developer & Programmer » JDeveloper, Java & XML » XML File generation
icon1.gif  XML File generation [message #325515] Fri, 06 June 2008 08:21 Go to next message
ukgweb
Messages: 13
Registered: June 2008
Location: cyberspace
Junior Member
Hi
Please can any one give me the pls/sql trigger code to generate an xml file for a table.Say for example employees table.
Thanks
Re: Trigger [message #325516 is a reply to message #325515] Fri, 06 June 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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
icon5.gif  Re: Trigger [message #325519 is a reply to message #325515] Fri, 06 June 2008 08:37 Go to previous messageGo to next message
ukgweb
Messages: 13
Registered: June 2008
Location: cyberspace
Junior Member
Hi
thank you for your response but:

i typed in the following query
select dbms_xmlquery.getxml('select * from employees where salary <= 3000') from dual;

but i got the following error:

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

please help

Thanks
Re: Trigger [message #325525 is a reply to message #325519] Fri, 06 June 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version?

Regards
Michel
Re: Trigger [message #325527 is a reply to message #325525] Fri, 06 June 2008 08:55 Go to previous messageGo to next message
ukgweb
Messages: 13
Registered: June 2008
Location: cyberspace
Junior Member
Hi

Oracle 10G XE

Thanks
Re: Trigger [message #325530 is a reply to message #325527] Fri, 06 June 2008 09:13 Go to previous messageGo to next message
ukgweb
Messages: 13
Registered: June 2008
Location: cyberspace
Junior Member
Hi
Wanted To generate a XML File

i typed in the following query
select dbms_xmlquery.getxml('select * from employees where salary <= 3000') from dual;

but i got the following error:

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

please help
I am using Oracle 10G XE

Thanks
icon5.gif  XML File generation [message #325534 is a reply to message #325515] Fri, 06 June 2008 09:20 Go to previous messageGo to next message
ukgweb
Messages: 13
Registered: June 2008
Location: cyberspace
Junior Member
Hi
Wanted To generate a XML File

i typed in the following query
select dbms_xmlquery.getxml('select * from employees where salary <= 3000') from dual;

but i got the following error:

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

please help
I am using Oracle 10G XE

Thanks
Re: XML File generation [message #325539 is a reply to message #325534] Fri, 06 June 2008 09:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
SQL> set long 9999

SQL> desc dbms_xmlquery;
ERROR:
ORA-04043: object dbms_xmlquery does not exist

SQL> desc dbms_xmlgen;
PROCEDURE CLEARBINDVALUES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CTX                            NUMBER                  IN
....

SQL> select dbms_xmlgen.getxml('select * from emp where rownum < 3') from dual;

DBMS_XMLGEN.GETXML('SELECT*F
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <JOB>PRESIDENT</JOB>
  <HIREDATE>17-NOV-81</HIREDATE>
  <SAL>50000</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>

DBMS_XMLGEN.GETXML('SELECT*F
--------------------------------------------------------------------------------
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>01-MAY-81</HIREDATE>
  <SAL>28500</SAL>
  <DEPTNO>30</DEPTNO>
 </ROW>
</ROWSET>


Regards

Raj
icon10.gif  Re: XML File generation [message #325666 is a reply to message #325515] Sat, 07 June 2008 07:25 Go to previous messageGo to next message
ukgweb
Messages: 13
Registered: June 2008
Location: cyberspace
Junior Member
Hi Rajaram

Simply great stuff man

Thanks a lot
Re: XML File generation [message #325683 is a reply to message #325666] Sat, 07 June 2008 09:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have to use XML you should consider to install xml query.

Regards
Michel
Re: XML File generation [message #520725 is a reply to message #325683] Wed, 24 August 2011 05:18 Go to previous messageGo to next message
anupamhazari
Messages: 2
Registered: April 2006
Junior Member
I was doing comparative analysis of dbms_xmlquery and dbms_xmlgen and i observed that if there is a data element with apostrophe in it e.g. O'Hare then dbms_xmlquery captures it as O'Hare and dbms_xmlgen gives O&apos;Hare, which looks clean to me.
Re: XML File generation [message #520727 is a reply to message #520725] Wed, 24 August 2011 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there any question or is this just an information you give?

Regards
Michel
Re: XML File generation [message #520731 is a reply to message #520727] Wed, 24 August 2011 06:05 Go to previous messageGo to next message
anupamhazari
Messages: 2
Registered: April 2006
Junior Member
its not a question, making some contribution.
Re: XML File generation [message #520735 is a reply to message #520731] Wed, 24 August 2011 06:40 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for it.

Regards
Michel
Previous Topic: ADF
Next Topic: Is it Possible
Goto Forum:
  


Current Time: Thu Mar 28 10:18:40 CDT 2024