quote:

---

Originally posted by sunitabeck

You can't really compare an XML column, so you will have to query it to see if the data matches the input. When you query, how you would do it depends on the details of your requirements and data - for example,

a) is order important? (i.e., is 210,214,215 the same as 210,215,214)?

b) are you only able to get the input in comma-separated form, or are you able to get it in tabular form?

---

---------------------------------------------------------------------------------
-- TEST DATA
CREATE TABLE #tmp(x XML);
INSERT INTO #tmp VALUES (
'<values>
<valueid>210</valueid>
<valueid>214</valueid>
</values>'),
('<values>
<valueid>11</valueid>
<valueid>34</valueid>
</values>'),
('<values>
<valueid>67</valueid>
<valueid>66</valueid>
</values>'),
('<values>
<valueid>210</valueid>
<valueid>214</valueid>
<valueid>215</valueid>
</values>');

---------------------------------------------------------------------------------
-- Comma-separated input string.
DECLARE @var VARCHAR(32); SET @var = '210,214,215';

-- query
;WITH cte1 AS
(
	SELECT x,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
	FROM #tmp
),
cte2 AS
(
	SELECT
		c1.RN,
		ROW_NUMBER() OVER (PARTITION BY RN ORDER BY (SELECT NULL)) AS RN2,
		c1.x,
		CAST(c.query('data(.)') AS VARCHAR(32)) val
	FROM
		cte1 c1
		CROSS APPLY x.nodes('//valueid') T(c)
)
SELECT
	x
FROM
	cte2 cc1
WHERE
	NOT EXISTS
	(
		SELECT val FROM cte2 cc2 WHERE cc1.RN = cc2.RN
		EXCEPT
		SELECT Item from dbo.DelimitedSplit8K(@var,',')
	)
	AND 
	NOT EXISTS
	(
		SELECT Item from dbo.DelimitedSplit8K(@var,',')
		EXCEPT
		SELECT val FROM cte2 cc2 WHERE cc1.RN = cc2.RN
	)
	AND RN2 = 1;
---------------------------------------------------------------------------------

DROP TABLE #tmp;