sql - Oracle 11g 的 XML 表

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

谢谢

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 上尝试这个，在 10g 中使用 left outer join 似乎有问题，但显然应该是在 11g 中没问题。