Create Oracle XMLTYPE from CLOB specifying character set

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).

I am looking at the documentation here: docs.oracle.com/database/121/ARPLS/t_xml.htm#ARPLS71959 and I don't see the option you mention. Where did you find that overload? Are you sure it was for Oracle (and not, for example, for Java or who knows what else)? — user5683823
– user5683823, Commented Aug 23, 2017 at 16:17
Your CLOB is plain ASCII - why do you need to specify any character set? — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 23, 2017 at 16:31
Look for csid IN number argument, see docs.oracle.com/database/121/ARPLS/t_xml.htm#ARPLS71959. <node1><node2>... is just an example. createXML will be executed with a column which contain special characters which can't be decoded with database NLS_CHARACTERSET character set. — DevDav
– DevDav, Commented Aug 23, 2017 at 18:07
Then you must provide a BLOB not a CLOB. Have a look at DBMS_LOB.CONVERTTOBLOB to convert given CLOB into BLOB. — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 23, 2017 at 20:31

Wernfried Domscheit · Accepted Answer · 2017-08-24 07:32:56Z

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

Kaushik Nayak · Accepted Answer · 2017-08-23 16:18:01Z

0

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;

answered Aug 23, 2017 at 16:18

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Create Oracle XMLTYPE from CLOB specifying character set

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related