如何在Oracle XMLTABLE中包含对象列表？

您可以使用链接的XMLTable调用：

select t1.name, t1.age, t2.some_number
from my_with_clause
cross join xmltable (
  '/root/person'
  passing xmltype(my_with_clause.my_xml)
  columns name varchar2(20) path 'name',
    age number path 'age',
    list_of_numbers xmltype path 'list_of_numbers/number'
) t1
cross join xmltable (
  '/number'
  passing t1.list_of_numbers
  columns some_number number path '.'
) t2;

NAME                        AGE SOME_NUMBER
-------------------- ---------- -----------
Miguel Martins               32           1
Miguel Martins               32           2
Another Person               19           3
Another Person               19           4

SQL Fiddle不喜欢那样，但是db <> fiddle喜欢，它在本地针对11gR2起作用。（实际上，SQL Fiddle可以使用真正的表而不是CTE ...）

要么

select t1.name, t1.age, t2.some_number
from my_with_clause
cross join xmltable (
  '/root/person'
  passing xmltype(my_with_clause.my_xml)
  columns name varchar2(20) path 'name',
    age number path 'age',
    list_of_numbers xmltype path 'list_of_numbers'
) t1
cross join xmltable (
  '/list_of_numbers/number'
  passing t1.list_of_numbers
  columns some_number number path '.'
) t2;

db <>小提琴

使用此XML，您还可以通过从数字开始然后在节点中查找其他数据来对一个XMLTable进行处理：

select t1.name, t1.age, t1.some_number
from my_with_clause
cross join xmltable (
  '/root/person/list_of_numbers/number'
  passing xmltype(my_with_clause.my_xml)
  columns name varchar2(20) path './../../name',
    age number path './../../age',
    some_number number path '.'
) t1;

NAME                        AGE SOME_NUMBER
-------------------- ---------- -----------
Miguel Martins               32           1
Miguel Martins               32           2
Another Person               19           3
Another Person               19           4

SQL Fiddle和db <> fiddle。

但是您实际的（非最小的）XML可能不实用。

这也适用于具有多行的表，而不仅仅是CTE或具有单个XML值要解包的表。

如果您可能遇到list_of_names缺少或为空的情况，并且仍想显示名称/年龄，则可以使用外部联接而不是交叉联接，但是它需要一个丑陋的on 1=1子句。SQL Fiddle显示了这种数据的交叉联接和左联接，但是如果可以的话，我会避免使用左联接方法。

如果您使用的是12c或更高的温度，则可以使用outer apply代替left join ... on 1=1，这反而不太令人反感。（并且，如果您不必担心缺少数字，可以使用@Lukaszcross apply代替cross join它，这似乎无济于事。）

ORA-19025很有趣。SQL Fiddle正在运行Oracle Database 11g Express Edition 11.2.0.2.0版。在企业版11.2.0.4中，您的代码得到

ORA-19279：XPTY0004-XQuery动态类型不匹配：预期的单例序列-获得了多项目序列

相反，但这是相同的问题；number每个人下都有多个节点，并且不知道如何处理它们，这是默认的数据类型-由于您尚未指定数据类型，因此所有内容都以字符串形式返回。在我的第一个版本中，我使用相同的路径，但将该列声明为XMLType，因此在第一个版本中，您将数字作为XML片段获得：

<number>1</number><number>2</number>

或第二个：

<list_of_numbers><number>1</number><number>2</number></list_of_numbers>

然后，可以通过链接的XMLTable调用来消耗那些。