时间戳

首页 > 解决方案 > 如何在 Oracle 的 XMLTABLE 中包含对象列表？

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

问题描述

我有以下（最小）XML：

<root>
  <person>
    <name>Miguel Martins</name>
    <age>32</age>
    <list_of_numbers>
      <number>1</number>
      <number>2</number>
    </list_of_numbers>
  </person>

  <person>
    <name>Another Person</name>
    <age>19</age>
    <list_of_numbers>
      <number>3</number>
      <number>4</number>
    </list_of_numbers>
  </person>
</root>

以及以下查询：

with my_with_clause as
 (select '
<root>
  <person>
    <name>Miguel Martins</name>
    <age>32</age>
    <list_of_numbers>
      <number>1</number>
      <number>2</number>
    </list_of_numbers>
  </person>

  <person>
    <name>Another Person</name>
    <age>19</age>
    <list_of_numbers>
      <number>3</number>
      <number>4</number>
    </list_of_numbers>
  </person>
</root>
' my_xml
    from dual)
select t1.*
  from my_with_clause,
       xmltable('/root/person' passing xmltype(my_with_clause.my_xml) columns name path 'name', age path 'age') t1;

产生以下输出：

+----------------+-----+
|      Name      | Age |
+----------------+-----+
| Miguel Martins |  32 |
| Another Person |  19 |
+----------------+-----+

到目前为止，一切都很好。现在，我想将数字添加到别名为 T1 的表中。也就是说，我希望得到以下输出：

+----------------+-----+-------------+
|      Name      | Age | Some_Number |
+----------------+-----+-------------+
| Miguel Martins |  32 |           1 |
| Miguel Martins |  32 |           2 |
| Another Person |  19 |           3 |
| Another Person |  19 |           4 |
+----------------+-----+-------------+

我尝试将 some_number 列添加到 XMLTABLE。那是：

with my_with_clause as
 (select '
<root>
  <person>
    <name>Miguel Martins</name>
    <age>32</age>
    <list_of_numbers>
      <number>1</number>
      <number>2</number>
    </list_of_numbers>
  </person>

  <person>
    <name>Another Person</name>
    <age>19</age>
    <list_of_numbers>
      <number>3</number>
      <number>4</number>
    </list_of_numbers>
  </person>
</root>
' my_xml
    from dual)
select t1.*
  from my_with_clause,
       xmltable('/root/person' passing xmltype(my_with_clause.my_xml) columns name path 'name', age path 'age', some_number path 'list_of_numbers/number') t1;

但是，我没有得到想要的输出。相反，我收到以下错误：

ORA-19025: EXTRACTVALUE 仅返回一个节点的值

我怎样才能达到预期的输出？这是一个SQLFiddle供您试用（如有必要）。

标签： sqlxmloraclexmltable

解决方案

您可以使用链式 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，这不太令人反感。（如果您不必担心缺少数字，您可以使用cross apply而不是cross join@Lukasz 显示的那样 - 在这里似乎没有什么区别。）

ORA-19025 很有趣。SQL Fiddle 正在运行 Oracle Database 11g Express Edition Release 11.2.0.2.0。在 Enterprise Edition 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 调用使用。

推荐阅读