使用Oracle 11g的XMLtable [英] XMLtable with Oracle 11g

问题描述

这是一个示例表:

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 outer 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.

这篇关于使用Oracle 11g的XMLtable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！