SQL Server 2005 XML Query problem with ".exist" method

I have this XML Query in SQL Server 2005:

SElECT XmlField FROM tablename WHERE xmlField.exist('(/Root/Name[id="10")[1]') = 1

However, I want to replace the value "10" with a parameter that I pass to the Stored Procedure. How do I achieve this? I开发者_如何学JAVA have tried using "@variablename" but it doesn't work.

Thanks in advance.

Probably, you want to have something like

SELECT XmlField FROM tablename WHERE xmlField.exist('(/Root/Name[id="{ sql:variable("@variablename") }")[1]') = 1

See http://msdn.microsoft.com/en-us/library/ms188254(v=SQL.100).aspx for how to access variables and columns in XQuery in SQL Server.

After a few minutes of hair pulling...i found an answer...

Result_XML.exist('(/Root/Name[id="{sql:variable("@myId")}"])[1]') = 1

should be written as

Result_XML.exist('(/Root/Name[id=(sql:variable("@myId"))])[1]') = 1

I replaced the "{ and }" with ( and ) to enclose the sql:variable keyword.

There is one more thing I found out about by many many trials: if your variable is a char value, if you declare it in your sql statement, it should be varchar, not char.

This sql didn't return any results:

DECLARE @myparam char(50)
SET @myparam = 'someval'
...
WHERE 
t.c.exist('/root/child[text() = sql:variable("@myparam ")]') = 1

But this did:

DECLARE @myparam varchar(50)
SET @myparam = 'someval'
...
WHERE 
t.c.exist('/root/child[text() = sql:variable("@myparam ")]') = 1

Maybe this is obvious, but I spent some time before I figured the reason why no records would be returned.