带有 Oracle 11g 的 XMLtable
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9321391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XMLtable with Oracle 11g
提问by John
Here is a sample table:
这是一个示例表：
create table xmltemp (mydoc xmltype)
Here is a small xml doc for it:
这是一个小的 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>
')
)
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
I tried this to get both country and states, but it seems oracle does not like the '..' syntax:
我试过这个来获得国家和州，但似乎 oracle 不喜欢 '..' 语法：
select
statename
from xmltemp,
xmltable('/countries/country/states/state/name' passing mydoc
columns statename varchar2(20) path '.',
countryname varchar2(20) path '../../../name') c
Heres the error:
这是错误：
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
Here is that error:
这是那个错误：
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
Thanks
谢谢
回答by John Doyle
Try this:
尝试这个：
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
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 joinin 10g but apparently it should be fine in 11g.
因为您有多个状态，所以您应该加入另一个 xml 表。由于某些国家/地区没有状态，因此它需要是左外连接。我正在使用旧方法，(+)因为我正在 10g 上尝试此方法，似乎left outer join在 10g 中使用存在问题，但显然在11g.
