title: SQL Server XML Column exist() query
body: <p>Assuming I have a SQL Server 2005 table with an xml column  containing the following values: </p>
  
  <pre><code>CREATE TABLE XmlTest (
     XMLid int,
     Data xml)
  
  INSERT XMLTest 
  VALUES ( 1 , '&lt;data&gt;&lt;item&gt;&lt;type v="1" /&gt;&lt;value v="12.56" /&gt;&lt;/item&gt;&lt;item&gt;&lt;type     v="3" /&gt;&lt;value v="DEBIT" /&gt;&lt;/item&gt;&lt;/data&gt;' )
  
  INSERT XMLTest 
  VALUES ( 2 , '&lt;data&gt;&lt;item&gt;&lt;type v="1" /&gt;&lt;value v="99.22" /&gt;&lt;/item&gt;&lt;item&gt;&lt;type v="3" /&gt;&lt;value v="CREDIT" /&gt;&lt;/item&gt;&lt;/data&gt;' )
  
  INSERT XMLTest 
  VALUES ( 3 , '&lt;data&gt;&lt;item&gt;&lt;type v="3" /&gt;&lt;value v="12.56" /&gt;&lt;/item&gt;&lt;item&gt;&lt;type v="1" /&gt;&lt;value v="DEBIT" /&gt;&lt;/item&gt;&lt;/data&gt;' )
  </code></pre>
  
  <p>I want to test for the existance of item elements with a type v="3" AND a value v="DEBIT".</p>
  
  <p>I am using the exist() function as follows:</p>
  
  <pre><code>SELECT *
  FROM XmlTest
  WHERE Data.exist('/data/item/type[@v=''3'']') = 1
  AND Data.exist('/data/item/value[@v=''DEBIT'']') = 1
  </code></pre>
  
  <p>However this brings me back rows with XMLid 1 and 3. </p>
  
  <p>Can anyone outline what change I need to make to my WHERE clause to return only record which have an item where the type node v value is 3 and the value node v value is "DEBIT"? i.e. only record with XMLid 1</p>
  
  <p>Thanks</p>