XMLTABLE, ORA-19011: Character string buffer too small

Question

I am trying to query xml data in OracleDB with the next structure:

<?xml version="1.0" encoding="UTF-8"?>
<clearing xmlns="http://some-url.com" >
  <file_id>2207150000023097</file_id>
  <file_type>FLTP1710</file_type>
  <start_date>2022-01-01</start_date>
  <end_date>2022-07-15</end_date>
  <inst_id>7029</inst_id>
  <file_date>2022-07-15</file_date>
  <operation>
    <oper_id>2233020001111683</oper_id>
    <oper_type>OPTP0020</oper_type>
    <msg_type>MSGTPRES</msg_type>
    <sttl_type>STTT0100</sttl_type>
    <oper_date>2022-02-07T00:00:00</oper_date>
    </operation>
    <operation>
    ...
    </operation>
    ...
</clearing>
.....

DDL for the table:

CREATE TABLE xml_tab (
  id        NUMBER,
  xml_data  XMLTYPE
);

I am using this for querying:

WITH
  operation_data AS
   (SELECT xt.*
    FROM   xml_tab x,
           XMLTABLE('/clearing/operation'
             PASSING xmltype(replace(x.xml_data,'xmlns=','removed='))
             COLUMNS
               oper_id          VARCHAR2(100)   PATH 'oper_id',
               transactions     XMLTYPE          PATH 'transaction'
             ) xt
    WHERE ID = 1
   )     ,
 transactions_data AS
  (SELECT oper_id,
           xt2.*
    FROM   operation_data dd,
           XMLTABLE('/transaction'
             PASSING dd.transactions
             COLUMNS
               transaction_id                       VARCHAR2(100)  PATH 'transaction_id'
             ) xt2
  )
  select * from  transactions_data;

but the query fails with SQL Error [19011] [72000]: ORA-19011: Character string buffer too small. I can understand that the culprit here is this line: PASSING xmltype(replace(x.xml_data,'xmlns=','removed=')) , the line is required to replace 'xmlns' to 'removed=' in the "clearing" tag for querying, but apparently the xml data is too huge for the replacement procedure. How could I work around the "ORA-19011: Character string buffer" in my query?

I've seen the question about the error here:ORA-19011: Character string buffer too small but it's for XMLElement and that's not exactly my case.

Answer 1

By default

replace(x.xml_data,'xmlns=','removed=')

will try to extract the XML document as a varchar2, and if it is more than 4000 (or 32000 depending on Oracle version and settings) characters (or bytes) then you will get that error, as the value is just too big for that data type.

If you explicitly extract the XML as a CLOB instead then it will work:

replace(x.xml_data.getclobval(),'xmlns=','removed=')

making that part of the query:

           XMLTABLE('/clearing/operation'
             PASSING xmltype(replace(x.xml_data.getclobval(),'xmlns=','removed='))
             COLUMNS

But it would be simpler to just include the namespace in your XMLTable calls:

WITH
  operation_data AS
   (SELECT xt.*
    FROM   xml_tab x,
           XMLTABLE(
             XMLNAMESPACES(default 'http://some-url.com'),
             '/clearing/operation'
             PASSING x.xml_data
             COLUMNS
               oper_id          VARCHAR2(100)   PATH 'oper_id',
               transactions     XMLTYPE          PATH 'transaction'
             ) xt
    WHERE ID = 1
   )     ,
 transactions_data AS
  (SELECT oper_id,
           xt2.*
    FROM   operation_data dd,
           XMLTABLE(XMLNAMESPACES(default 'http://some-url.com'),
             '/transaction'
             PASSING dd.transactions
             COLUMNS
               transaction_id                       VARCHAR2(100)  PATH 'transaction_id'
             ) xt2
  )
  select * from  transactions_data;

db<>fiddle

You don't really need the CTEs; it would be even simpler if you just join things directly:

SELECT op.oper_id, tr.transaction_id
FROM   xml_tab x
CROSS APPLY
       XMLTABLE(
         XMLNAMESPACES(default 'http://some-url.com'),
         '/clearing/operation'
         PASSING x.xml_data
         COLUMNS
           oper_id          VARCHAR2(100)   PATH 'oper_id',
           transactions     XMLTYPE         PATH 'transaction'
         ) op
CROSS APPLY
       XMLTABLE(XMLNAMESPACES(default 'http://some-url.com'),
         '/transaction'
         PASSING op.transactions
         COLUMNS
           transaction_id  VARCHAR2(100)  PATH 'transaction_id'
         ) tr
WHERE x.ID = 1;

You can also then easily make those outer apply if you wanted to see operations without transactions, as your sample data had.

db<>fiddle