Home » SQL & PL/SQL » SQL & PL/SQL » create XML file from an Oracle Table
create XML file from an Oracle Table [message #310512] Tue, 01 April 2008 08:11 Go to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
Hello all,
i would like to create an XML file from a table in my Oracle database, i 'm new in using xml with oracle, so an exemple will help me very much,
thanks in advance.
Re: create XML file from an Oracle Table [message #310515 is a reply to message #310512] Tue, 01 April 2008 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them.

As you are an expert, I don't answer the question because you obviously already knows it.

Regards
Michel

[Updated on: Tue, 01 April 2008 08:23]

Report message to a moderator

Re: create XML file from an Oracle Table [message #310521 is a reply to message #310512] Tue, 01 April 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
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: create XML file from an Oracle Table [message #310528 is a reply to message #310521] Tue, 01 April 2008 09:08 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
thank very much, but my googl is to generate an xml file and save it in my disk, help me please. i work with oracle 10g XE/win XP
Re: create XML file from an Oracle Table [message #310530 is a reply to message #310528] Tue, 01 April 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus SPOOL command to save the result in file.

Regards
Michel
Re: create XML file from an Oracle Table [message #310531 is a reply to message #310528] Tue, 01 April 2008 09:15 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
i hava another broblem, wen i try this exemple, i get this error message:
ERREUR Ó la ligne 1 :
ORA-00904: "DBMS_XMLQUERY"."GETXML" : identificateur non valide
i d'ont know if oracle 10g know the package dbms_xmlquery.
thanks for any help.
Re: create XML file from an Oracle Table [message #310535 is a reply to message #310531] Tue, 01 April 2008 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your version with 4 decimals?

Regards
Michel
Re: create XML file from an Oracle Table [message #310538 is a reply to message #310535] Tue, 01 April 2008 09:41 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Re: create XML file from an Oracle Table [message #310619 is a reply to message #310538] Tue, 01 April 2008 15:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Dbms_xmlquery.getxml is available in 10g. You may need to grant privileges explicitly. Please post a copy and paste of an actual run of your test from SQL*Plus, including the resulting error.
Re: create XML file from an Oracle Table [message #310622 is a reply to message #310538] Tue, 01 April 2008 15:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Do you have an emp table in your schema? Try testing with the dual table instead:
SCOTT@orcl_11g> select dbms_xmlquery.getxml('select * from dual') from dual
  2  /

DBMS_XMLQUERY.GETXML('SELECT*FROMDUAL')
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <DUMMY>X</DUMMY>
   </ROW>
</ROWSET>


You may have done something like below:
SCOTT@orcl_11g> select dbms_xmlquery.getxml('select * from nonexistent_table') from dual
  2  /

DBMS_XMLQUERY.GETXML('SELECT*FROMNONEXISTENT_TABLE')
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: ORA-00942: table or view does not e
xist
</ERROR>


SCOTT@orcl_11g> 

Re: create XML file from an Oracle Table [message #310669 is a reply to message #310538] Wed, 02 April 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the following query:
SQL> select comp_id, version, status from dba_registry where comp_id='XML';
COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -----------
XML                            10.2.0.4.0                     VALID

1 row selected.

Regards
Michel
Re: create XML file from an Oracle Table [message #310744 is a reply to message #310669] Wed, 02 April 2008 04:45 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
i executed this query but there is nothing:
-------------------------------------------------------------------
SQL> select comp_id, version, status from dba_registry where comp_id='XML';

aucune ligne sÚlectionnÚe
-------------------------------------------------------------------


but i think that i have resolve my problem using this code:
-------------------------------------------------------------------


declare
rc sys_refcursor;
begin
open rc for select * from ( select * from emp );
dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , ‘UTL_XML’,'anton.xml’);
end;
-------------------------------------------------------------------
and it's work perfectly
Re: create XML file from an Oracle Table [message #310748 is a reply to message #310744] Wed, 02 April 2008 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Take care of "smart" quotes.

Regards
Michel
Re: create XML file from an Oracle Table [message #310751 is a reply to message #310748] Wed, 02 April 2008 05:12 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
I'm sorry, so the code is:

declare
    rc sys_refcursor;
    begin
      open rc for select * from ( select * from emp );
    dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , ‘UTL_XML’,'anton.xml’);
    end;
    

this generate an xml file : anton.xml, in the directory 'UTL_XML'.
Re: create XML file from an Oracle Table [message #310757 is a reply to message #310751] Wed, 02 April 2008 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, take care of smart quotes.
See the differences (only the first one is correct):
'
‘
’

Don't use MS/Word or the like to write your query, use simple editor like vi or notepad.

Thanks for the tip ("xmltype(rc).getclobval()").

Regards
Michel
Re: create XML file from an Oracle Table [message #310788 is a reply to message #310512] Wed, 02 April 2008 07:27 Go to previous messageGo to next message
jarbouni
Messages: 19
Registered: March 2008
Junior Member
ok Michel, thanks
Re: create XML file from an Oracle Table [message #324137 is a reply to message #310521] Sat, 31 May 2008 02:04 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
Michel Cadot wrote on Tue, 01 April 2008 08:29
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




Here, where the generated XML file is stored?
Re: create XML file from an Oracle Table [message #324155 is a reply to message #324137] Sat, 31 May 2008 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On the screen.
Use "spool" if you want to store it somewhere.

Regards
Michel
Re: create XML file from an Oracle Table [message #324272 is a reply to message #324155] Sun, 01 June 2008 05:44 Go to previous message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
Michel Cadot wrote on Sat, 31 May 2008 03:41
On the screen.
Use "spool" if you want to store it somewhere.

Regards
Michel




Yeah Michel..i forgot to do that..Thank you
Previous Topic: Partitioning & Truncating a Table based on date in Oracle 10 g
Next Topic: PL/block not working
Goto Forum:
  


Current Time: Tue Dec 06 12:31:15 CST 2016

Total time taken to generate the page: 0.07166 seconds