Create Oracle XMLTYPE from CLOB specifying charact

Question

I'm trying to create XMLTYPE from CLOB column and specify character set explicitly. I've found there is an overloaded XMLTYPE.createXML function which accepts character set but when I execute passing additional arguments I get an error. Why?

SELECT
XMLTYPE.createXML(TO_CLOB ('<node1><node2>the &#180; character</node2></node1>'),NLS_CHARSET_ID('AL32UTF8'),'',1,1)  
from dual;

error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'CREATEXML'

The reason why I bother passing the character set is, CLOB column contains characters which are encoded with different character set than the database character set (which for example doesn't support #180).

Answer 1

The reason why I bother passing the character set is, CLOB column contains characters which are encoded with different character set than the database character set (which for example doesn't support #180).

This one I don't understand. #180; is simple plain ASCII, it should work for any condition.

Simply run

SELECT
    XMLTYPE.createXML(TO_CLOB('<node1><node2>the &#180; character</node2></node1>'))
from dual;

or even shorter

SELECT
    XMLTYPE('<node1><node2>the &#180; character</node2></node1>')
from dual;

Now, let's assume your XML contains characters which are not supported by database character set, in this case your XML could be <node1><node2>the ´ character</node2></node1> for example.

First of all you cannot store (or use) any character in CLOB (or VARCHAR2) which is not supported by database character set - never! You must use NCLOB (or NVARCHAR2) which are based on National Database Character Set and typically support any Unicode character.

You can specify character set in XMLTYPE.createXML(), however then you must provide XML as BLOB. You could do it like this:

DECLARE
    xmlString NCLOB := '<node1><node2>the '||NCHR(180)||' character</node2></node1>';
    xmlDoc XMLTYPE;     
    xmlBinary BLOB;

    lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    dest_offset INTEGER := 1;
    src_offset INTEGER := 1;
    read_offset INTEGER := 1;
    warning INTEGER;

BEGIN

    DBMS_LOB.CREATETEMPORARY(xmlBinary, TRUE);
    DBMS_LOB.CONVERTTOBLOB(xmlBinary, xmlString, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, 2000, lang_context, warning);
    xmlDoc := XMLTYPE.createXML(xmlBinary, 2000, NULL, 1, 1);
END;

2000 is the csid of your national database character set. Use

SELECT PARAMETER, VALUE, NLS_CHARSET_ID(VALUE) 
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER LIKE '%CHARACTERSET';

to get your ID's.

Some notes:

I tried with string N'<node1><node2>the ´ character</node2></node1>', however Oracle replaced ´ immediately with ¿. I did not manage to enter ´ directly.

Almost all XML Functions return VARCAHR2 values (not NVARCAHR2), also most of XMLTYPE member functions work with CLOB (not NCLOB). If you just read and store your XML documents as XMLTYPE in your database it should be fine, however as soon you start any operations with these data sooner or later you will hit a conversion error. You should really consider to migrate your database character set, see Character Set Migration and/or Oracle Database Migration Assistant for Unicode

Answer 2

You can directly use the XMLType function

SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>'
  ||TO_CLOB ('<node1><node2>the &#180; character</node2></node1>')) myxml
FROM dual;