Strange error with XMLTABLE on Oracle 11g
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