Oracle 11g中的XMLtable

Question

这里是一个示例表格：

create table xmltemp (mydoc xmltype)

这是一个小的 XML 文档：

insert into xmltemp values (
xmltype
('<?xml version="1.0"?>
<countries>
  <country>
    <name>Canada</name>
  </country>
  <country>
    <name>US</name>
    <states>
      <state>
        <name>Washington</name>
        <name>Oregon</name>        
      </state>
    </states>
  </country>
</countries>
')
)  

请注意，加拿大没有“州”元素，但美国有。 我正在尝试获取这些查询结果（顺序和格式不重要）：

Canada,
US,Washington
US,Oregon

当我执行此操作时，结果中会同时出现加拿大和美国。

select
countryname
from xmltemp,
xmltable('/countries/country' passing mydoc
   columns countryname varchar2(10) path 'name') 

当我这样做时，我得到了两个状态：

select
statename
from xmltemp,
xmltable('/countries/country/states/state/name' passing mydoc
   columns statename   varchar2(20) path '.') c

我尝试使用“..”语法来获取国家和州，但似乎Oracle不支持这种语法。

select
statename
from xmltemp,
xmltable('/countries/country/states/state/name' passing mydoc
   columns statename   varchar2(20) path '.',
           countryname varchar2(20) path '../../../name') c

这是错误信息：

ORA-19110: unsupported XQuery expression

当我尝试这样做时，由于存在两个状态，我会收到“多项”错误提示：

select
countryname,
statename
from xmltemp,
xmltable('/countries/country' passing mydoc
   columns countryname varchar2(10) path 'name',
           statename   varchar2(20) path 'states/state/name') c

这是错误信息：

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton 
sequence - got multi-item sequence

这是一个查询，可以让我获得所需的输出结果：

Canada,
US,Washington
US,Oregon

谢谢

- John

Answer 1

试试这个：

select      X.COUNTRYNAME, Y.STATENAME
from        XMLTEMP
           ,xmltable('/countries/country'
                     passing MYDOC
                     columns COUNTRYNAME varchar2(20) path './name', 
                             STATES xmltype path './states') X,
            xmltable('/states/state/name' passing X.STATES 
                    columns STATENAME varchar2(20) path '.') (+) Y

因为您有多个州，所以应该加入另一个xml表。由于某些国家没有州，因此需要进行左外连接。我正在使用旧的(+)方法，因为我在尝试10g时似乎使用left outer join存在问题，但显然在11g中应该没问题。

- John Doyle

---

谢谢。昨天我考虑了一下外连接，但是还没有完全弄明白。虽然这两个链接不是针对Oracle的，但我觉得它们也很有帮助：第一部分 第二部分。 - John

这似乎是正确的答案，因为我找不到11g以任何形式在PATH子句中支持父级寻址。基本上，这里所做的是将父记录加载到XMLTABLE中，并将子元素包含在该结果中的XMLTYPE列中。然后，使用来自父记录的XMLTYPE子列，将子记录加载到另一个XMLTABLE中。然后Oracle隐式地连接所有表。--- 然而，我发现11g不能正确地使用只有一个(+)的外连接，我必须在x和y表上都放置(+)，这没有太多意义。 - juanitogan

我在11g中发现的另一个奇怪问题是，当使用默认的xml命名空间时，我无法像预期的那样通过特定属性来访问元素。解决方法是使用通配符命名空间来访问属性（显式命名空间也不起作用）。例如，以下语句不起作用：PATH 'ID[@type="user"]'。以下语句也不起作用：PATH 'ID[@foo:type="user"]'。以下语句可以正常工作：PATH 'ID[@*:type="user"]'。 - juanitogan