XPath to check if all provided pairs of elements exist for an entry

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'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.

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

How would I check for matches with multiple attributes?

I'm trying to find rows where: (Key=Animal & Value=Rat) AND (Key=Color & Value=Black).

(Yes, I could put another .exist with an AND in my WHERE clause but I'm trying to avoid that)

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.

Answer 1

XPath allows nested predicates and multiple predicates. You could write

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

or equivalently, but somewhat shorter

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