6

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
8

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

enter image description here


Query plan with text in the predicate AllValues.exist('*[text() = "POSITIVE"]') = 1

enter image description here

5

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

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

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.

NUTS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.