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?

Copycat

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. – Trichomoniasis

I think there is bug. When I add NVL to expression - xmltype(NVL(d.x,'<null/>')) then query executes, but don't return any row. with data as ( select '<A><B>B21</B></A>' x from dual ), extractedxml as ( SELECT b FROM data d, xmltable('/A/B' PASSING xmltype(NVL(d.x,'<null/>')) COLUMNS b varchar2(20) PATH '.') ) select b from extractedxml UNION select b from extractedxml – Conception

@Asieh: when editing, please note that inline code spans (like this) shouldn't be used for highlighting, only for code in sentences. Also, please try and improve the post as much as possible when editing to save the reviewers time. Thanks! – Kimberleekimberley

Answer 1

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

Dogcatcher