How to query against exact values in XML column set

Question

I have a table that contains 80+ sparse columns along with a column set column, this is a brief example:

DROP TABLE IF EXISTS #ColumnSet
GO

CREATE TABLE #ColumnSet
(
    Id        INT          NOT NULL
  , Value1    VARCHAR(100) SPARSE NULL 
  , Value2    VARCHAR(100) SPARSE NULL 
  , Value3    VARCHAR(100) SPARSE NULL 
  , Value4    VARCHAR(100) SPARSE NULL 
  , AllValues XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO

INSERT INTO #ColumnSet
(Id, Value1, Value2, Value3, Value4)

VALUES
(1, 'POSITIVE', NULL, NULL, NULL),
(2, 'NEGATIVE', NULL, 'NEGATIVE', NULL),
(3, NULL, NULL, 'NEGATIVE', 'POSITIVE'),
(4, 'NEGATIVE', NULL, 'THIS IS NOT A POSITIVE RESULT', NULL)
GO

I want to query the column set to identify rows where any of the columns has a value of POSITIVE.

Using the value method on the column set will concatenate all the values together into one string and I could use LIKE but I don't want results where the value is within another string.

SELECT
    *

FROM
    #ColumnSet

WHERE
    AllValues.value('/', 'nvarchar(4000)') LIKE '%POSITIVE%'

Are there alternative methods of querying a column set to achieve the above? Using APPLY along with the nodes method provides the same concatenated string output though my syntax could be incorrect.

The required output :

id
1
3

score 9 · Accepted Answer · answered Aug 23 '18 at 08:46

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

score 6 · Answer 2 · answered Aug 21 '18 at 15:10

6

Here's an XPath solution, though I don't know how efficient it will be.

SELECT *
FROM #ColumnSet
WHERE AllValues.exist('//*[text() = "POSITIVE"]') = 1

answered Aug 21 '18 at 15:10

Forrest

4,189
1
20
31

Erik Reasonable Rates Darling · Answer 3 · 2018-08-23T11:22:48.857

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.

How to query against exact values in XML column set

3 Answers3