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