title: Retrieve Node Value from XML in XMLTable (Oracle 11gR2)
body: <p>hope this is something simple and I am just missing it, but consider this example:</p>
  
  <pre><code>with et as(
       SELECT 
             xmlType('&lt;Invoice&gt;
      &lt;InvoiceInformation&gt;
          &lt;Number&gt;123456&lt;/Number&gt;
      &lt;/InvoiceInformation&gt;
      &lt;InvoiceLines&gt;
          &lt;InvoiceLine&gt;
              &lt;Detail&gt;
                  &lt;Amount&gt;100&lt;/Amount&gt;
                  &lt;Line&gt;1&lt;/Line&gt;
              &lt;/Detail&gt;
              &lt;Type&gt;
                  &lt;CheesyPotato&gt;
                      &lt;Instructions&gt;
                          &lt;CookTime&gt;120&lt;/CookTime&gt;
                          &lt;CookTimeUnits&gt;Minutes&lt;/CookTimeUnits&gt;
                          &lt;CookTemperature&gt;450&lt;/CookTemperature&gt;
                      &lt;/Instructions&gt;
                  &lt;/CheesyPotato&gt;
              &lt;/Type&gt;
          &lt;/InvoiceLine&gt;
          &lt;InvoiceLine&gt;
              &lt;Detail&gt;
                  &lt;Amount&gt;10000&lt;/Amount&gt;
                  &lt;Line&gt;2&lt;/Line&gt;
              &lt;/Detail&gt;
              &lt;Type&gt;
                  &lt;DeathStar&gt;
                      &lt;Instructions&gt;
                          &lt;CookTime&gt;4&lt;/CookTime&gt;
                          &lt;CookTimeUnits&gt;5 "parsecs"&lt;/CookTimeUnits&gt;
                          &lt;CookTemperature&gt;1000000&lt;/CookTemperature&gt;
                      &lt;/Instructions&gt;
                  &lt;/DeathStar&gt;
              &lt;/Type&gt;
          &lt;/InvoiceLine&gt;  
          &lt;InvoiceLine&gt;
              &lt;Detail&gt;
                  &lt;Amount&gt;250&lt;/Amount&gt;
                  &lt;Line&gt;3&lt;/Line&gt;
              &lt;/Detail&gt;
              &lt;Type&gt;
                  &lt;Quiche&gt;
                      &lt;Instructions&gt;
                          &lt;CookTime&gt;75&lt;/CookTime&gt;
                          &lt;CookTimeUnits&gt;Minutes&lt;/CookTimeUnits&gt;
                          &lt;CookTemperature&gt;350&lt;/CookTemperature&gt;
                      &lt;/Instructions&gt;
                  &lt;/Quiche&gt;
              &lt;/Type&gt;      
          &lt;/InvoiceLine&gt;    
      &lt;/InvoiceLines&gt;  
  &lt;/Invoice&gt;  
   ') xt
        from dual
         ) 
          SELECT     
              ext.*
             FROM
             et,
             XMLTABLE(
             'for $Invoice in $INV/Invoice
                  for $InvoiceItem in $Invoice/InvoiceLines/InvoiceLine
                    return &lt;row&gt; 
                    {
                      $Invoice
                      ,$InvoiceItem
                    } 
                    &lt;/row&gt;'           
                PASSING et.xt as INV
                      COLUMNS
                 INVOICENUMBER                   VARCHAR2  (6)       PATH 'Invoice/InvoiceInformation/Number'                    
                ,InvoiceLineNumber               VARCHAR2  (5)       PATH 'InvoiceLine/Detail/Line'
                ,Amount                          VARCHAR2  (5)       PATH 'InvoiceLine/Detail/Amount'
                ,CookTime                        VARCHAR2  (5)       PATH 'InvoiceLine/Type//Instructions/CookTime'
                ,CookTimeUnits                   VARCHAR2  (15)       PATH 'InvoiceLine/Type//Instructions/CookTimeUnits'
                ,CookTemperature                 VARCHAR2  (10)       PATH 'InvoiceLine/Type//Instructions/CookTemperature'
              ) ext
  /            
  </code></pre>
  
  <p>--Give me these results                  </p>
  
  <pre><code>    INVOICENUMBER INVOICELINENUMBER AMOUNT COOKTIME COOKTIMEUNITS   COOKTEMPERATURE 
  ------------- ----------------- ------ -------- --------------- --------------- 
  123456        1                 100    120      Minutes         450             
  123456        2                 10000  4        5 "parsecs"     1000000         
  123456        3                 250    75       Minutes         350
  </code></pre>
  
  <p>However I would like to, in this query, get the Type value. Thus How do I get the child node of Type's "Name" so my results would be as such?  </p>
  
  <pre><code>TypeChild       INVOICENUMBER INVOICELINENUMBER AMOUNT COOKTIME COOKTIMEUNITS   COOKTEMPERATURE 
  ------------------- ------------- ----------------- ------ -------- --------------- --------------- 
  CheesyPotato    123456        1                 100    120      Minutes         450             
  DeathStar       123456        2                 10000  4        5 "parsecs"     1000000         
  Quiche          123456        3                 250    75       Minutes         350
  </code></pre>
  
  <p>I have tried various methods; along the lines of ",$InvoiceItem/Type/child/name" to no avail, any help would be appreciated, thanks</p>
  
  <p>Or am I simply going at this the incorrect way?  (what cannot be changed, however, is that I will need to be able to consume this XML into a query!)</p>