Loading

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:




0 Responses to “How to write a XML file in Oracle with specific character set using DBMS_XMLDOM.WRITETOFILE”

Post a Comment

Disclaimer

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.