XPath to check if all provided item pairs exist for writing

Question

I have the following XML stored in an XML field in a SQL Server 2008 database.

<attributes>
  <attribute>
    <key>animal</key>
    <value>rat</value>
  </attribute>
  <attribute>
    <key>color</key>
    <value>black</value>
  </attribute>
</attributes>

I can select all the records from the table with the key "animal" with the value "rat" with the following xpath in sql.

SELECT *
FROM XmlEvents
WHERE Attributes.exist('/attributes/attribute[key="animal" and value="rat"]') = 1

How to check matches with multiple attributes?

I am trying to find lines where: (Key = Animal and Value = Rat) AND (Key = Color and Value = Black).

(Yes, I could put another .exist with the AND clause in my WHERE, but I try to avoid this)

Bonus points, if you can lead me in the right direction to make this kind of dynamic. I would like to have a stored procedure or function that could somehow take a list of key / value pairs and find all the lines that match all the ones provided.

Answer 1

XPath .

/attributes[attribute[key="animal" and value="rat"]][attribute[key="color" and value="black"]]

,

/attributes[attribute[key="animal"]/value="rat" and attribute[key="color"]/value="black"]