Exporting data from Oracle to a XML

Please provide more information about your exact requirements.

If could be as simple as:
select dbms_xmlgen.getxml('select * from some_table') from dual;

or very detailed using the XMLELEMENT/XMLAGG/XMLFOREST/etc... SQL functions.

Simple as select dbms_xmlgen.getxml('select * from some_table') from dual;

Thanks.

I also need to know how to dumo the xml file to a remote server.
Thanks.

>>how to dumo the xml file to a remote server.

Again, it depends on how you do things.

I would use sqlplus to spool out the output and ftp/rcp the file.

How to setup the spooling automatically?

You could generate the XML into a clob and FTP the clob directly to the remote site


An example of sending a clob is in the following article:

https://www.experts-exchange.com/Database/Oracle/A_3043-How-to-FTP-with-Oracle-PL-SQL.html

There is no 'automatic' spooling.

In addition to spooling, you need to turn off some features of sqlplus and set up the environment to meet your needs.

If you have questions about what any of the below things do, the online docs has all the information:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037682

In sqlplus:
set lines 1000
set trimspool on
set pages 0
set feedback off

spool myfile.xml
select dbms_xmlgen.getxml('select sysdate from from dual') from dual;
spool off

sdstuber,

The sample code you provided is a trigger stored in oracle?
thanks.

no it's a package, but it is stored in oracle

I use the following sql but gave me error on my single quote. how to fix it?

select dbms_xmlgen.getxml('select * from TBL1 where active='Y'') from dual;

try:
select dbms_xmlgen.getxml('select * from TBL1 where active=''Y''') from dual;

note: those are ALL single quotes.  No double quotes.

You need two single quotes to get one in a string.

I tried the followings in SQL Plus worksheet

set linesize 1000;
set trimspool on;
set pages 0;
set feedback off;

spool C:\DATA.xml;
select dbms_xmlgen.getxml('select * from TBL1 where active="Y"') from dual;
spool off;


Got the following errors.

ERROR:
ORA-19202: Error occurred in XML processing
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_Y'
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1

You added double quotes not two single quotes.

Change the one " to TWO '

Should I see the actual data in the XML output file?
Below are what I got in XML.

select dbms_xmlgen.getxml('select * from TBL1 where status=''ACTIVE''') from dual;
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <REGION_CODE>RU</REGION_CODE>
  <FAC_ID>

spool off;

sdstuber,

I assume the sdsftp code you suggested will generate XML file and put it on a remote FTP site.
Can we set up a schedule/time which your code will run?

Thanks.

Mark

sdsftp itself doesn't generate the XML, but the example I posted above ( http:#a40244478 )  shows one way to generate the xml and then use sdsftp to move the xml to a remote site.  

Note -there is no file anywhere except on the remote site using that method.

You can use a similar anonymous block inside a dbms_job or dbms_scheduler job.

Best would be to wrap it in a procedure and then use dbms_scheduler to run the procedure at whatever schedule you needed.

Can you provide a sample code for using dbms_scheduler to run such stored procedures?
Thanks.

I opened another question at the link below. Please help if you can. thanks.

https://www.experts-exchange.com/questions/28506085/dbms-scheduler-in-Oracle.html

There are TONS of examples out there.

I would start with the online docs for dbms_scheduler.create_job:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72235

An example is in another doc:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN12384