How to query against exact values in XML column set
Specifying the text()
node before the predicate will be more efficient than having text()
in the predicate.
select *
from #ColumnSet as C
where AllValues.exist('*/text()[. = "POSITIVE"]') = 1
Query plan with text in the predicate AllValues.exist('*[text() = "POSITIVE"]') = 1
Here's an XPath solution, though I don't know how efficient it will be.
SELECT *
FROM #ColumnSet
WHERE AllValues.exist('//*[text() = "POSITIVE"]') = 1
Here's another xquery solution:
SELECT *, cs.AllValues.query('. [contains(., "POSITIVE")]')
FROM #ColumnSet AS cs
WHERE cs.AllValues.exist('. [contains(., "POSITIVE")]') = 1
Since you changed your sample data, the above won't work.
You could do this instead:
SELECT cs.*
FROM #ColumnSet AS cs
CROSS APPLY cs.AllValues.nodes('/*') AS x(c)
WHERE x.c.exist('text()[. = "POSITIVE"]') = 1;
But I'm not sure how it will compete with Mikael's answer at scale.