DevOps, CI/CD and Automation

Hello (XML) Experts

I need your help with manipulating a BLOB column containing XML data - I am encountering the following error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00200: could not convert from encoding UTF-8 to WINDOWS-1252
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 283

I am on Windows 7 64 bit, Oracle 11.2.0.3 64 bit and database character set is WE8MSWIN1252, NLS_LANG is set to AMERICAN_AMERICA.AL32UTF8. The BLOB column contains the following XML data:

<?xml version="1.0" encoding="utf-8"?>
<Root CRC="-4065505">
  <Header Converted="0">
    <Version Type="String" Value="512" />
    <Revision Type="String" Value="29" />
    <SunSystemsVersion Type="String" Value="" />
    <Date Type="String" Value="20080724" />
    <Time Type="String" Value="165953" />
    <DAG Type="String" Value="" />
    <ChkID Type="String" Value="" />
    <FormType Type="String" Value="1" />
    <DB Type="String" Value="AllBusinessUnits" />
    <FuncID Type="String" Value="SOE" />
    <Status Type="String" Value="" />
    <FileType Type="String" Value="SFL" />
    <Descriptions>
      <Default Type="String" Value="Sales Order Entry" />
      <L01 Type="String" Value="Sales Order Entry" />
      <L33 Type="String" Value="Saisie commande client" />
      <L34 Type="String" Value="Entrada de órdenes de venta" />
      <L39 Type="String" Value="Inserimento ordine di vendita" />
      <L49 Type="String" Value="Aufträge erfassen" />
      <L55 Type="String" Value="Entrada de pedido de venda" />
      <L81 Type="String" Value="�注オーダー入力" />
      <L86 Type="String" Value="销售订�录入" />
      <L87 Type="String" Value="銷售訂單錄入" />
    </Descriptions>
  </Header>
<FormDesignerAppVer Type="String" Value="5.1" SFLOnly="1" />
</Root>

I am using the XMLTYPE constructor and passing in the BLOB column and the character set id of the XML data stored in the BLOB column in order to extract and update a node in the XML as follows:

select xmltype(srce_form_detail,873) from SRCE_FORM 

where 873 above corresponds to the utf-8 encoding of the XML data in the BLOB column i.e. AL32UTF8, but this results in the above error.

I have also tried converting the BLOB to a CLOB first as below where BLOB2CLOB is a function that converts the BLOB to a CLOB:

select xmltype(BLOB2CLOB(srce_form_detail)).EXTRACT('/Root/Header/DB').getStringVal() XMLSrc  from SRCE_FORM;

This results in the following error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '¿'
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

Looking at the XML in the BLOB I noticed that it contains a BOM(byte order mark) and this is causing the XML parsing to fail and I don't know how to deal with it and I don't want to simply SUBSTR it out.

What I am trying to achieve is to extract the contents of the DB node in the XML and depending on its value I need to update the 'Value' part of that node. I am stuck at the point of extracting the contents of the DB node.

I hope I have provided enough information and I would appreciate any suggestions on how best to resolve this - my XML knowledge is very limited so I would appreciate any help.

Regards,
Mohinder