Use XML Query as Part of a WHERE Predicate
I need to include an XML query as part of a WHERE predicate in a SELECT statement.
I have this structure:
DECLARE @tmp TABLE (typeId int, xmlCol xml);
INSERT INTO @tmp
(typeId, xmlCol)
VALUES
(1, '<search><groups><g id="25" /><g id="26" /></groups></search>'),
(1, '<search><groups><g id="250" /><g id="9" /></groups></search>'),
(2, '<search><groups><g id="25" /><g id="12" /><g id="125" /></groups></search>');
SELECT * FROM @tmp;
But I need to pull out the lines where typeId=1
AND where the XML data contains <g id="25" />
. So in my example, I only saw the first row in the result set.
I would prefer an XML query rather than casting for nvarchar and using LIKE
if possible. I tried this but just got a syntax error:
SELECT
*
FROM
@tmp
WHERE
(typeId = 1) AND
(xmlCol.query('/search/groups/g[@id=25])'))
A non-boolean expression specified in a context where a is expected to have a condition close to ')'.
I have searched for examples but cannot find XML queries used this way.
source to share
Use xmlcol.exist
. Something like that.
DECLARE @tmp TABLE (typeId int, xmlCol xml);
INSERT INTO @tmp
(typeId, xmlCol)
VALUES
(1, '<search><groups><g id="25" /><g id="26" /></groups></search>'),
(1, '<search><groups><g id="250" /><g id="9" /></groups></search>'),
(2, '<search><groups><g id="25" /><g id="12" /><g id="125" /></groups></search>');
DECLARE @id int = 25
SELECT * FROM @tmp
WHERE typeId = 1
AND xmlCol.exist('search/groups/g[@id= sql:variable("@id")]') = 1
source to share