sql - Oracle 11g 上 XMLTABLE 的奇怪错误

Question

我正在使用 Oracle 11.2.0.4.0并且在 XMLTABLE 时遇到过几次问题被卷入。我的最新问题可以通过以下示例(我设计得尽可能简单)来演示:

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;

产生以下错误:

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

而以下查询按预期工作(with 子句未更改):

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

此外，如果使用 XMLTABLE，则查询有效避免:

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

所以我有一个解决方法，我会避免使用 XMLTABLE只要我不理解上述行为。是 XMLTABLE被认为是 buggy 还是我错过了什么？

Answer 1

根据我的经验，在 xmltable 中为序数添加另一列是个好主意。

此 SQL 语句工作正常:

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;

省略序数列导致的另一个致命问题:

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 (!)

但

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