title: XMLtable with Oracle 11g
body: <p>Here is a sample table:</p>
  
  <pre><code>create table xmltemp (mydoc xmltype)
  </code></pre>
  
  <p>Here is a small xml doc for it:</p>
  
  <pre><code>insert into xmltemp values (
  xmltype
  ('&lt;?xml version="1.0"?&gt;
  &lt;countries&gt;
    &lt;country&gt;
      &lt;name&gt;Canada&lt;/name&gt;
    &lt;/country&gt;
    &lt;country&gt;
      &lt;name&gt;US&lt;/name&gt;
      &lt;states&gt;
        &lt;state&gt;
          &lt;name&gt;Washington&lt;/name&gt;
          &lt;name&gt;Oregon&lt;/name&gt;        
        &lt;/state&gt;
      &lt;/states&gt;
    &lt;/country&gt;
  &lt;/countries&gt;
  ')
  )  
  </code></pre>
  
  <p>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):</p>
  
  <pre><code>Canada,
  US,Washington
  US,Oregon
  </code></pre>
  
  <p>When I execute this, I see both Canada and the US in the result:</p>
  
  <pre><code>select
  countryname
  from xmltemp,
  xmltable('/countries/country' passing mydoc
     columns countryname varchar2(10) path 'name') 
  </code></pre>
  
  <p>When I do this, I get both the states:</p>
  
  <pre><code>select
  statename
  from xmltemp,
  xmltable('/countries/country/states/state/name' passing mydoc
     columns statename   varchar2(20) path '.') c
  </code></pre>
  
  <p>I tried this to get both country and states, but it seems oracle does not like the '..' syntax:</p>
  
  <pre><code>select
  statename
  from xmltemp,
  xmltable('/countries/country/states/state/name' passing mydoc
     columns statename   varchar2(20) path '.',
             countryname varchar2(20) path '../../../name') c
  </code></pre>
  
  <p>Heres the error:</p>
  
  <pre><code>ORA-19110: unsupported XQuery expression
  </code></pre>
  
  <p>When I try this, I get the 'multi-item' error because of the two states:</p>
  
  <pre><code>select
  countryname,
  statename
  from xmltemp,
  xmltable('/countries/country' passing mydoc
     columns countryname varchar2(10) path 'name',
             statename   varchar2(20) path 'states/state/name') c
  </code></pre>
  
  <p>Here is that error:</p>
  
  <pre><code>ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton 
  sequence - got multi-item sequence
  </code></pre>
  
  <p>What's a query that will get me my desired output of:</p>
  
  <pre><code>Canada,
  US,Washington
  US,Oregon
  </code></pre>
  
  <p>Thanks</p>