Friday, January 10, 2014
How to write a XML file in Oracle with specific character set using DBMS_XMLDOM.WRITETOFILE
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.
Specification:
DBMS_XMLDOM.WRITETOFILE
( doc
IN DOMDOCUMENT,
fileName
IN VARCHAR2,
charset
IN VARCHAR2
);
Sample
Script:
SET SERVEROUTPUT ON;
DECLARE
lv_doc DBMS_XMLDOM.domdocument;
lv_xdata XMLTYPE;
lv_characterset VARCHAR2 (50) DEFAULT 'ISO-8859-1';
lv_filename VARCHAR2 (50) DEFAULT 'SHAREORACLEAPPS.xml';
lv_directory VARCHAR2 (50) DEFAULT 'TEST_DIRECTORY'; -- db directory
BEGIN
SELECT XMLELEMENT ("AP_INVOICE_DATA",
XMLELEMENT ("Invoice_number", invoice_num),
XMLELEMENT ("Invoice_date", invoice_date),
XMLELEMENT ("Invoice_amount", invoice_amount)
)
INTO lv_xdata
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
DBMS_XMLDOM.writetofile (lv_doc,
lv_directory || '/' || lv_filename,
lv_characterset
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Others Exception : ' || SQLERRM);
END;
Sample XML:
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “How to write a XML file in Oracle with specific character set using DBMS_XMLDOM.WRITETOFILE”
Post a Comment