Friday, January 10, 2014
In our project, we had a requirement to export an AP Invoice data as an XML file in a specified CHARACTER SET. We gone through different options and finally got a standard ORACLE API. “DBMS_XMLDOM.WRITETOFILE”. Here we have given a brief overview of it.
( doc IN DOMDOCUMENT,
fileName IN VARCHAR2,
charset IN VARCHAR2
SET SERVEROUTPUT ON;
lv_characterset VARCHAR2 (50) DEFAULT 'ISO-8859-1';
lv_filename VARCHAR2 (50) DEFAULT 'SHAREORACLEAPPS.xml';
lv_directory VARCHAR2 (50) DEFAULT 'TEST_DIRECTORY'; -- db directory
SELECT XMLELEMENT ("AP_INVOICE_DATA",
XMLELEMENT ("Invoice_number", invoice_num),
XMLELEMENT ("Invoice_date", invoice_date),
XMLELEMENT ("Invoice_amount", invoice_amount)
FROM ap_invoices_all aia
WHERE aia.invoice_num LIKE '%TEST_1';
-- prepare the document
lv_doc := DBMS_XMLDOM.newdomdocument (lv_xdata);
-- write the xml file to a db directory in a specified chracter set
lv_directory || '/' || lv_filename,
DBMS_OUTPUT.put_line ('Others Exception : ' || SQLERRM);
Do you think this Article is useful?
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.