Oracle - extracting XML version and encoding from XML prolog in XMLType

user_11511039 0
Viewed 65

Let's say we have following XMLType in Oracle:

<?xml version="1.0" encoding="UTF-8"?>
<root>
</root>

I need to get XML version and encoding values or whole XML prolog preferably with XML functions, f. ex. XMLTABLE. Is that possible? Or only with string/CLOB functions?

We use Oracle 19c.

plsql oracle xml sql
1 Answers

Is that possible?

No, unfortunately, it isn't, because XML functions like XMLTable work with XMLType which parses your input xml (from your NLS_LANG charset if it is CLOB or Varchar2, and from specified charset if it is BLOB) and store it in own internal codepage and when you get it, oracle returns it in your NLS_LANG:

SQL> with v as (select q'[<?xml version="1.1" encoding="blabla"?><root/>]' as vXML from dual)
  2  select xmltype(vXML) x from v;

X
------------------------------------------------
<?xml version="1.1" encoding="US-ASCII"?>
<root/>

And look what if I change my NLS_LANG to UTF8:

SQL> with v as (select q'[<?xml version="1.1" encoding="blabla"?><root/>]' as vXML from dual)
  2  select xmltype(vXML) x from v;

X
-----------------------------------------
<?xml version="1.1" encoding="UTF-8"?>
<root/>


SQL> ! env | grep NLS_LANG
NLS_LANG=american_america.UTF8
user_4291000
Related