Home > HowTo, Miscellaneous > How To : Write XML to file with encoding specification

How To : Write XML to file with encoding specification

November 23, 2011 Leave a comment Go to comments

DBMS_XSLPROCESSOR.clob2file procedure provides a very easy way to write a CLOB to a file.
I use it extensively with XML content generated from the database, but each time I have to manually add the XML prolog with the proper encoding specification reflecting the actual character set :

DECLARE

  doc    CLOB;

BEGIN

  SELECT XMLSerialize(DOCUMENT
           XMLElement("Employees",
             XMLAgg(
               XMLElement("Employee",
                 XMLAttributes(empno as "id")
               , XMLForest(
                   ename as "name"
                 , job   as "job"
                 , sal   as "salary"
                 )
               )
               order by empno
             )
           )
         as CLOB
         )
  INTO doc
  FROM scott.emp
  ;

  DBMS_XSLPROCESSOR.clob2file( '<?xml version="1.0" encoding="UTF-8"?>' || doc
                             , 'TEST_DIR'
                             , 'employees.xml'
                             , nls_charset_id('AL32UTF8') );

END;
/

So I wrote this little wrapper procedure to handle that automatically by passing in the ISO charset name :

CREATE OR REPLACE PROCEDURE writeXMLClob2File (
  p_directory  IN VARCHAR2
, p_filename   IN VARCHAR2
, p_xmldoc     IN CLOB
, p_encoding   IN VARCHAR2 DEFAULT NULL
, p_standalone IN BOOLEAN  DEFAULT NULL
)
IS

  v_csid       BINARY_INTEGER;
  v_encoding   VARCHAR2(30);
  v_prolog     VARCHAR2(80);

BEGIN

  IF p_encoding IS NOT NULL THEN
   
    v_csid := nls_charset_id(utl_i18n.map_charset(p_encoding, utl_i18n.GENERIC_CONTEXT, utl_i18n.IANA_TO_ORACLE));
    v_encoding := p_encoding;

  ELSE
   
    v_csid := nls_charset_id('CHAR_CS');
    v_encoding := utl_i18n.map_charset(nls_charset_name(v_csid));
    
  END IF;

  IF v_csid IS NULL THEN
   
    raise_application_error(-20001, 'Invalid encoding specification '''||p_encoding||'''');
  
  END IF;

  v_prolog := '<?xml version="1.0" encoding="' || v_encoding || '"'
           || case when p_standalone is not null then ' standalone="'|| case when p_standalone then 'yes' else 'no' end || '"' end
           || '?>';

  dbms_xslprocessor.clob2file(
    flocation => p_directory
  , fname     => p_filename
  , cl        => v_prolog || p_xmldoc
  , csid      => v_csid
  );

END;

How does it work?

The procedure calls the UTL_I18N.map_charset function to translate the ISO charset name to the Oracle equivalent, e.g. ISO-8859-15 to WE8ISO8859P15, which is then used to retrieve the internal Oracle charset ID necessary to write the file with the required encoding.
If we don’t pass any encoding, the database character set is used by default and the UTL_I18N.map_charset function is called the other way around, converting the Oracle charset name to the ISO one.
The procedure also handles the “standalone” specification.

Advertisements
  1. AlexAnd
    October 8, 2012 at 12:32

    hi Marc

    what do you want about creating FAQ on forums.oracle.com for XML branch
    like *** SQL and PL/SQL Frequenty Asked Questions (FAQ) *** https://forums.oracle.com/forums/forum.jspa?forumID=75&start=0 ?

    any ideas to mail ;)

    AlexAnd

  2. July 9, 2014 at 11:09

    Thanks for giving code.
    Oracle Training in Chennai

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: