Home » SQL & PL/SQL » SQL & PL/SQL » How can generate dump into XML format?
How can generate dump into XML format? [message #213141] Tue, 09 January 2007 11:24 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I would like to know that how can I generate output in XML format from SQL*PLUS?
I know that TOAD or any another tool can do that and also know that I can generate extract in HTML format throuh SQL*PLUS but don't know how to generate in XML format?

Thanks in advance!
Poratips
Re: How can generate dump into XML format? [message #213147 is a reply to message #213141] Tue, 09 January 2007 11:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
select xmlgen.getxml('select * from dept') from dual;

should help ( i have not tested it yet)
Re: How can generate dump into XML format? [message #213150 is a reply to message #213147] Tue, 09 January 2007 11:50 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you very much.
I am getting following error so assuming that it's 10 features and I have Oracle 9i R2.

Thanks,
Poratips
Re: How can generate dump into XML format? [message #213152 is a reply to message #213150] Tue, 09 January 2007 12:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What error?
Did you install xmldb?
Re: How can generate dump into XML format? [message #213153 is a reply to message #213152] Tue, 09 January 2007 12:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
My mistake.
you need to use dbms_xmlgen.getxml
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
scott@9i > set long 1000
scott@9i > set pages 1000
scott@9i > 1
  1* select dbms_xmlgen.getxml(' select * from dept') from dual
scott@9i > /

DBMS_XMLGEN.GETXML('SELECT*FROMDEPT')
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>

Re: How can generate dump into XML format? [message #213162 is a reply to message #213153] Tue, 09 January 2007 12:48 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks once again.
You real rock it, it works like charm.
Do I need any another setting in order to extract more fields or more records like 20k to 30k?

Thanks,
Poratips
Re: How can generate dump into XML format? [message #213168 is a reply to message #213162] Tue, 09 January 2007 12:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
play around with these for display.
set long 1000
set pages 1000

and you might want to use UTL_FILE to write to the OS file.
Re: How can generate dump into XML format? [message #213185 is a reply to message #213168] Tue, 09 January 2007 14:12 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks much!
I can spool the file and run the ftp job then it will be fine right? If I don't use UTL_FILe?

thanks,
Poratips
Re: How can generate dump into XML format? [message #213206 is a reply to message #213185] Tue, 09 January 2007 16:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is upto you. For really large volumes UTL_FILE is better.

[Updated on: Tue, 09 January 2007 18:33]

Report message to a moderator

Re: How can generate dump into XML format? [message #213208 is a reply to message #213206] Tue, 09 January 2007 17:56 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, Actually I have to pull the extract from one db server and ftp to another apps server so developer can use that file and develope the reports.

Thanks,
Poratips
Previous Topic: How 2 c d structure of view....
Next Topic: Connect by help 2
Goto Forum:
  


Current Time: Sat Dec 03 06:05:55 CST 2016

Total time taken to generate the page: 0.08099 seconds