XMLtable with Oracle 11g

Question

Here is a sample table:

create table xmltemp (mydoc xmltype)

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

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

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

Solution

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 join in 10g but apparently it should be fine in 11g.