title: XPath to check if all provided pairs of elements exist for an entry
body: <p>I have the following XML stored in an XML field in a SQL Server 2008 database.</p>
  
  <pre><code>&lt;attributes&gt;
    &lt;attribute&gt;
      &lt;key&gt;animal&lt;/key&gt;
      &lt;value&gt;rat&lt;/value&gt;
    &lt;/attribute&gt;
    &lt;attribute&gt;
      &lt;key&gt;color&lt;/key&gt;
      &lt;value&gt;black&lt;/value&gt;
    &lt;/attribute&gt;
  &lt;/attributes&gt;
  </code></pre>
  
  <p>I'm able to select all the entries from the table which have an 'animal' key with a value of 'rats' with the following xpath in sql.</p>
  
  <pre><code>SELECT *
  FROM XmlEvents
  WHERE Attributes.exist('/attributes/attribute[key="animal" and value="rat"]') = 1
  </code></pre>
  
  <p>How would I check for matches with multiple attributes? </p>
  
  <p>I'm trying to find rows where: (Key=Animal &amp; Value=Rat) AND (Key=Color &amp; Value=Black).</p>
  
  <p><em>(Yes, I could put another .exist with an AND in my WHERE clause but I'm trying to avoid that)</em></p>
  
  <p>Bonus points if you can lead me in the right direction to make this some-what dynamic. I would love to have a stored procedure or function which could somehow take a list of Key/Value pairs and find all the rows which match everything provided.</p>