Querying XML Data Using SQL Parameters

Question

I am trying to query XML data in a table using a SQL parameter containing the search criteria and I am getting unexpected results.

As an example, consider the following T-SQL code:

DECLARE @Configuration AS TABLE
(
    [Data] XML NOT NULL
);

INSERT INTO Configuration ([Data]) VALUES '<Configuration><Colors><Color>RED</Color><Color>BLUE</Color></Colors></Configuration>');

DECLARE @Color AS NVARCHAR(MAX);

SET @Color = N'YELLOW';

SELECT COUNT(*) FROM @Configuration WHERE [Data].exist('/Configuration/Colors/Color/text() = sql:variable("@Color")') = 1;

I would expect the result of this query to return 0. In fact, this query returns 1. Even worse, it appears to return one regardless of what value I set @Color to.

What am I missing?

techspider · Accepted Answer · 2016-08-09 19:30:16Z

3

You have missing [ in your query

SELECT COUNT(*) FROM @Configuration WHERE [Data].exist('/Configuration/Colors/Color[text() = sql:variable("@Color")]') = 1;

XPath expressions are supposed to be enclosed between [ and ]. For more information on XPath and writing expressions, please refer this tutorial page.

edited Aug 9, 2016 at 19:30

answered Aug 9, 2016 at 19:07

techspider

3,40814 gold badges39 silver badges66 bronze badges

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

9 Comments

Martin Smith Over a year ago

Any idea what the actual semantics are of the query in the OP? What is it actually evaluating the exist against?

techspider Over a year ago

@MartinSmith I believe he is trying to find if the given parameter value exists in the XML

Martin Smith Over a year ago

That wasn't what I asked. I asked what the query is currently doing. As it doesn't raise any error. Is it just basically evaluating the existence of a Boolean result?

techspider Over a year ago

@MartinSmith Not sure what you are asking; Please fill-in additional details whatever I have misunderstood or incorrectly answered; I was only concerned to fix the issue he had

Jason Richmeier Over a year ago

I am not sure what is being asked either (perhaps I left some detail off of my original question). The answer provided here is what I was looking for. As such, I have marked it as the accepted solution. Thank you for the help.

|

Collectives™ on Stack Overflow

Querying XML Data Using SQL Parameters

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related