xmloracle-databaseblobxmltype

How do I pull individual XML values from an existing BLOB


I have a blob which I don't own and cannot change. It is a BLOB containing an XML message which I want to parse.

xml from schema browser


My parse attempt is as follows:

SELECT
   XMLTYPE( t.intfc_rqst_xml ).EXTRACT('//*/text()').getStringVal() 
FROM
   INTERFACE_MESSAGE_INFO t;

Gives this Error

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

Any help appreciated!


Solution

  • Character set is a second parameter.

    You can try the following:

    ...
    xmltype(t.intfc_rqst_xml , nls_charset_id('AL32UTF8'))
    ...
    

    Different Character Sets

    IANA Oracle
    UTF-8 AL32UTF8
    ISO-8859-1 WE8ISO8859P1
    ISO-8859-15 WE8ISO8859P15
    WINDOWS-1252 WE8MSWIN1252