Strange error with XMLTABLE on Oracle 11g

Question

I am using Oracle 11.2.0.4.0 and have run several times into problems when XMLTABLE was involved. My latest problem can be demonstrated with the following example (which I designed to be as simple as possible):

with data as
(
  select '<A><B>B21</B></A>' x from dual
),
extractedxml as (
  SELECT b
  FROM data d,
       xmltable('/A/B' PASSING xmltype(d.x) COLUMNS b varchar2(20) PATH '.')
)
select b from extractedxml union 
select b from extractedxml;

produces the following error:

ORA-19032: Expected XML tag , got no content
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
19032. 00000 -  "Expected XML tag %s got %s"
*Cause:    When converting XML to object, a wrong tag name was present
*Action:   Pass a valid canonical XML that can map to the given object type

while the following query works as expected (the with clause is unchanged):

with data as
(
  select '<A><B>B21</B></A>' x from dual
),
extractedxml as (
  SELECT b
  FROM data d,
       xmltable('/A/B' PASSING xmltype(d.x) COLUMNS b varchar2(20) PATH '.')
)
select b from extractedxml;

B
--------------------
B21

Further the query works if the use of XMLTABLE is avoided:

with data as
(
  select '<A><B>B21</B></A>' x from dual
),
extractedxml as (
  SELECT cast (extractvalue(column_value,'B') as varchar2(20)) b
  FROM data, table(xmlsequence(extract(xmltype(data.x),'/A/B')))
)
select b from extractedxml union 
select b from extractedxml;

B
--------------------
B21

So I have a workaround and I will avoid using XMLTABLE as long as I don't understand the behavior described above. Is XMLTABLE to be considered buggy or am I missing something?

Reproducible in 11.2.0.3; on 10.2.0.5 it core-dumps (though with the addition of a NO_XML_QUERY_REWRITE hint to avoid bug 5963973 it shows this behaviour too). So looks like a bug and you should probably raise a service request to report it. — Alex Poole, Apr 21 '15 at 12:25
I think there is bug. When I add `NVL` to expression - `xmltype(NVL(d.x,''))` then query executes, but don't return any row. `with data as ( select 'B21' x from dual ), extractedxml as ( SELECT b FROM data d, xmltable('/A/B' PASSING xmltype(NVL(d.x,'')) COLUMNS b varchar2(20) PATH '.') ) select b from extractedxml UNION select b from extractedxml` — Janis Baiza, Apr 21 '15 at 12:51
@Asieh: when editing, please note that inline code spans (`like this`) [shouldn't be used for highlighting](http://meta.stackoverflow.com/q/254990), only for code in sentences. Also, please try and improve the post as much as possible when editing to save the reviewers time. Thanks! — Qantas 94 Heavy, Apr 21 '15 at 13:52

Frank Ockenfuss · Answer 1 · 2015-05-19T06:29:06.370

According to my experience it is a good idea to add a further column for ordinality to the xmltable.

This SQL statement works fine:

with data as
(
  select '<A><B>B21</B></A>' x from dual
),
extractedxml as (
  SELECT b
  FROM data d,
       xmltable('/A/B' PASSING xmltype(d.x) COLUMNS i FOR ORDINALITY, b varchar2(20) PATH '.')
)
select b from extractedxml union 
select b from extractedxml;

Another fatal problem caused by omiting the column for ordinality:

with data as
(
  select xmltype('<A><B>B21</B></A>') x from dual
),
extractedxml as (
  SELECT b
  FROM data d,
       xmltable('/A/B' PASSING d.x COLUMNS b varchar2(20) PATH '.')
)
select b from extractedxml union 
select b from extractedxml;

>> no result (!)

But

with data as
(
  select xmltype('<A><B>B21</B></A>') x from dual
),
extractedxml as (
  SELECT b
  FROM data d,
       xmltable('/A/B' PASSING d.x COLUMNS i FOR ORDINALITY, b varchar2(20) PATH '.')
)
select b from extractedxml union 
select b from extractedxml;

>> B21

Strange error with XMLTABLE on Oracle 11g

1 Answers1