Use XML query as part of WHERE predicate

Question

I need to include an XML query as part of the 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 rows where typeId=1 AND where the XML data contains <g id="25" />. So in my example I'd only see the first row in the result set.

I'd prefer an XML query rather than casting to nvarchar and using LIKE, if possible. I tried this but just get a syntax error:

SELECT
    *
FROM
    @tmp
WHERE
    (typeId = 1) AND
    (xmlCol.query('/search/groups/g[@id=25])'))

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

I've been searching for examples but cannot find XML queries being used in this manner.

Stavros Zotalis · Accepted Answer · 2015-04-28 13:28:59Z

3

I think you must use xmlCol.exist instead of query.

SELECT
    *
FROM
    @tmp
WHERE
    (typeId = 1) AND
    (xmlCol.exist('/search/groups/g[@id=25]') = 1)

edited Apr 28, 2015 at 13:28

answered Apr 28, 2015 at 13:25

Stavros Zotalis

7264 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EvilDr Over a year ago

That returns an error: XQuery [@tmp.xmlCol.exist()]: No more tokens expected at the end of the XQuery expression. Found ')'.

ughai · Accepted Answer · 2015-04-28 13:26:04Z

2

Use xmlcol.exist. Something like this.

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

answered Apr 28, 2015 at 13:26

ughai

9,9004 gold badges33 silver badges48 bronze badges

1 Comment

EvilDr Over a year ago

Lovely example with variable. Thank you very much.

Collectives™ on Stack Overflow

Use XML query as part of WHERE predicate

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related