xmlinsert-intoxmltype

inserting into binary xml from blob

trying to insert data from a table having blob column to a table having binary xml column

source table 

desc "T24"."FBNK_ACCOUNT_CLOSED"; 
Name Null? Type 
----------------------------------------- -------- ---------------------- 
------ 
RECID NOT NULL VARCHAR2(255) 
XMLRECORD BLOB

Target table 

SQL> desc "T24"."FBNK_ACCOUNT_CLOSED_TEMP"; 
Name Null? Type 
----------------------------------------- -------- ---------------------- 
------ 
RECID NOT NULL VARCHAR2(255) 
XMLRECORD XMLTYPE STORAGE BINARY

so i am trying to insert data as below but its failing 

SQL> insert into "T24"."FBNK_ACCOUNT_CLOSED_TEMP" select RECID,XMLRECORD 
from "T24"."FBNK_ACCOUNT_CLOSED" where rownum<10; 

insert into "T24"."FBNK_ACCOUNT_CLOSED_TEMP" select RECID,XMLRECORD from 
"T24"."FBNK_ACCOUNT_CLOSED" where rownum<10 
* 
ERROR at line 1: 
ORA-00932: inconsistent datatypes: expected - got BLOB

Document i am following is oracle document ( Doc ID 1405457.1 )

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=309572821111227&parent=SrDetailText&sourceId=3-19827644581&id=1405457.1&_afrWindowMode=0&_adf.ctrl-state=1qwexs4zw_510

below are the few lines from the oracle documents

" Use insert select as syntax to move the data from scott.tb blob into scott.po_bin XMLType table stored as Binary XML

SQL> insert into po_bin select xmltype(bc, 873) from scott.tb;

1 row created.

SQL> select * from po_bin;

SYS_NC_ROWINFO$
---------------------------------------------------------------------------- 
----
<?xml version="1.0" encoding="US-ASCII"?>
<PurchaseOrder>
  <Reference>ADAMS-20011127121040988PST</Reference>
  <Actions>
    <Action>
      <User>SCOTT</User>
      <Date>2002-03-31</Date>
    </Action>
  </Actions>
  <Reject/>
...
...

"

tried editing the code with few alterations still failing with other error number 

insert into "T24"."FBNK_ACCOUNT_CLOSED_TEMP" select recid, 
xmltype(xmlrecord, 873) from "T24"."FBNK_ACCOUNT_CLOSED" where rownum<1000 
* 
ERROR at line 1: 
ORA-31011: XML parsing failed 
ORA-19202: Error occurred in XML processing 
LPX-00210: expected '<' instead of '2' 
Error at line 1 
ORA-06512: at "SYS.XMLTYPE", line 283 
ORA-06512: at line 1
Solution

  • The Reason for this Failure is the table contains some invalid XML data. so while inserting the data into XML binary column verification fails for some rows . so this cant be done on this table. but my steps are correct as per the oracle document.