I received couple of emails asking me how to export a report to an .xml file. Therefore I decided to create a small package and a demo application showing how to do that.
You can have a look here:
and read the corresponding thread here:
Basically, you need to fetch the region source and replace bind variables with the v('ITEM') function first. Second, you use the DBMS_XMLQUERY.getxml package to transfer you SQL Query into a result set, formated as XML. After that, you just need to cut your result into smaller portions and create an .xml file out of it.
This will not work for lower versions than 2.2 or XE. For those, you need to replace the part of the package fetching the region source with this block:
WHERE ID = LTRIM (p_region, 'R')
AND page_id = p_page_id
AND flow_id = p_app_id;
and have the appropriate grants granted by sys on
GRANT SELECT ON flows_020100.wwv_flow_page_plugs TO <your_schema>
CREATE OR REPLACE PUBLIC SYNONYM wwv_flow_page_plugs FOR
where flows_020100 is your html_db schema.