CODEWITHSUNDEEP
sql-serverxmlt-sql
Jason Richmeier
Jason Richmeier

Reputation: 1645

Querying XML Data Using SQL Parameters

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?

Upvotes: 1

Views: 57

Answers (1)

techspider
techspider

Reputation: 3410

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.

Upvotes: 3

Related Questions