title: oracle xmlcast(xmlquery) in subselect
body: <p>I have a table (Oracle 11.2.0.2) with an xmltype column and other non-xmltype columns. I want to do a select based on the value of some of the non-xmltype columns plus the contents of the xmltype column.</p>
  
  <p>I've tried doing this using xmlquery in my select, but I'm getting some funny results. Here's a reduced version my data: an order table with the username and the order details.</p>
  
  <pre><code>-- create order table
  create table orders(username varchar2(20), order_data xmltype);
  
  -- an order entered by jim
  insert into orders values ('jim', xmltype('
  &lt;ord:order xmlns:ord="http://www.blah.com/order/1.0"&gt;
     &lt;ord:zip&gt;123&lt;/ord:zip&gt;
     &lt;ord:date_time&gt;2012-09-24T00:27:00&lt;/ord:date_time&gt;
     &lt;ord:item&gt;
        &lt;ord:product&gt;A&lt;/ord:product&gt;
        &lt;ord:quantity&gt;12&lt;/ord:quantity&gt;
     &lt;/ord:item&gt;
     &lt;ord:item&gt;
        &lt;ord:product&gt;B&lt;/ord:product&gt;
        &lt;ord:quantity&gt;34&lt;/ord:quantity&gt;
     &lt;/ord:item&gt;
  &lt;/ord:order&gt;'));
  
  -- an order entered by bob
  insert into orders values ('bob', xmltype('
  &lt;ord:order xmlns:ord="http://www.blah.com/order/1.0"&gt;
     &lt;ord:zip&gt;123&lt;/ord:zip&gt;
     &lt;ord:date_time&gt;2012-09-24T00:27:00&lt;/ord:date_time&gt;
     &lt;ord:item&gt;
        &lt;ord:product&gt;A&lt;/ord:product&gt;
        &lt;ord:quantity&gt;56&lt;/ord:quantity&gt;
     &lt;/ord:item&gt;
     &lt;ord:item&gt;
        &lt;ord:product&gt;C&lt;/ord:product&gt;
        &lt;ord:quantity&gt;78&lt;/ord:quantity&gt;
     &lt;/ord:item&gt;
  &lt;/ord:order&gt;'));
  </code></pre>
  
  <p>As an example query, I tried to find all orders entered by Bob which have more than 1 item by doing this:</p>
  
  <pre><code>SELECT username, itemcount
  FROM
    (SELECT username,
      XMLCAST(XMLQUERY('count(/*:order/*:item)' PASSING order_data RETURNING CONTENT) AS  NUMBER) itemcount
    FROM orders
    )
  WHERE username = 'bob'
  AND itemcount &gt; 1
  </code></pre>
  
  <p>but it doesn't return any results, however if I comment out the last line (AND itemcount > 1) I get:</p>
  
  <pre><code>username | linecount
  --------------------
  bob      |         2
  </code></pre>
  
  <p>...I'm not sure why that is, I thought maybe it wasn't treating it as the correct type but I'm casting it as a number with the xmlcast.</p>
  
  <p>What's more puzzling is if I set the last line to be:</p>
  
  <pre><code>AND itemcount = 2
  </code></pre>
  
  <p>...it disappears again, but if I change it to:</p>
  
  <pre><code>AND itemcount != -7
  </code></pre>
  
  <p>...it reappears in the results, but with a value of zero:</p>
  
  <pre><code>username | linecount
  --------------------
  bob      |         0
  </code></pre>
  
  <p>So, I'm puzzled - I must be doing something wrong but I can't see what it is.</p>
  
  <p>I've found a workaround using xmltable which works fine, so that'll do me:</p>
  
  <pre><code>SELECT username, X.*
  FROM orders,
    XMLTABLE ( 
        xmlnamespaces ('http://www.blah.com/order/1.0' AS "ord" ),
        '/ord:order' PASSING order_data
            COLUMNS 
                itemcount number PATH 'count(ord:item)' ) AS X
  WHERE username = 'bob'
  AND itemcount &gt; 1
  </code></pre>