带有 Oracle 11g 的 XMLtable

2021-12-30 00:00:00 xml xpath sql oracle11g oracle

这是一个示例表:

create table xmltemp (mydoc xmltype)

这是一个小的 xml 文档:

Here is a small xml doc for it:

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>
')
)

请注意，加拿大没有州"元素，但美国有.我正在尝试获取这些查询结果(顺序和格式并不重要):

Notice that Canada does not have a 'states' element but the US does. I'm trying to get these query results (order and formatting is not important):

Canada,
US,Washington
US,Oregon

当我执行这个时，我在结果中看到加拿大和美国:

When I execute this, I see both Canada and the US in the result:

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

当我这样做时，我得到了两种状态:

When I do this, I get both the states:

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

我试过这个来获取国家和州，但似乎 oracle 不喜欢 '..' 语法:

I tried this to get both country and states, but it seems oracle does not like the '..' syntax:

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

当我尝试此操作时，由于两种状态，我收到多项目"错误:

When I try this, I get the 'multi-item' error because of the two states:

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

什么是可以得到我想要的输出的查询:

What's a query that will get me my desired output of:

Canada,
US,Washington
US,Oregon

谢谢

推荐答案

试试这个:

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 external join 存在问题，但显然它应该是11g 很好.

Because you have multiple states you should join to another xml table. As some countries have no states then it needs to be a left outer join. I'm using the old method of (+) as I'm trying this on 10g and it seems there's a problem using left outer join in 10g but apparently it should be fine in 11g.

相关文章