SQL & PL/SQL

Database: Oracle 11g

I'm building a procedure which produces a xml message based on data from a table.
To create the message I'm using XMLROOT and XMLELEMENT.

The system that receives the messages expects special characters like ë ï è, etc. to be escaped/replaced by a numerical character code.
For instance, ë should be ë (see a full list here: http://www.december.com/html/spec/codes.html)
I was wondering if there's a standard oracle function/procedure to do this.

I could of course do it myself, the codes are just the ascii codes, and I'm basically only concerned about values between the ascii codes 192 and 255, so I could create a function to do something like this:

SET SERVEROUTPUT ON;

DECLARE
   l_theString      VARCHAR2 (20) := 'Wêîrd strïng';
   l_theNewString   VARCHAR2 (100);
   l_character      VARCHAR2 (1);
BEGIN
   FOR i IN 1 .. LENGTH (l_theString)
   LOOP
      l_character := SUBSTR (l_theString, i, 1);

      IF ASCII (l_character) BETWEEN 192 AND 255
      THEN
         l_theNewString := l_theNewString || '&#' || ASCII (l_character) || ';';
      ELSE
         l_theNewString := l_theNewString || SUBSTR (l_theString, i, 1);
      END IF;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE (l_theNewString);
END;

But this seems a bit cumbersome and will definitely be a performance hit when I have to do it on a few thousands fields.

Edited by: Erik_NL on 20-apr-2012 6:49