Friday, 16 March 2007

Export to XML format

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:

SELECT plug_source
INTO v_sql
FROM wwv_flow_page_plugs
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>



where flows_020100 is your html_db schema.

1 comment:

Patrick Wolf said...

Hi Denes,

a really nice solution!

I looking through the code and saw two small improvements:
1) Use WWV_Flow.trim_sql to replace same default substitutions like #OWNER# in the SQL statement. It will also replace &XXX. references.

2) For the DBMS_XMLQUERY.getxml you can also use it as normal function, you don't have to select from dual.

BTW, maybe you should include a warning, that the code will not work for reports where the SQL statement is returned by a function.