XQeury with parameter in a DataSet

Today, I had to write a query that would retrieve a customerId from an xml data field in a SQL Server datatable. First of all, I noticed that the XQuery is case sensitive. After figuring this out, I started writing the query in SQL Server Management Studio and it started somewhat like this:

DECLARE @customerId NVARCHAR(1024)
SET @customerId = 'some subscriber id'

SELECT Id, Metadata
FROM Customers
WHERE Metadata.exist('/customerMetadata/subscriber[@Id=”@customerId"]') = 1

But wait… How do I make the distinction between an xml attribute and a variable? After googling for a while, I learned that I had to use the “sql:variable(‘@customerId’) construct for this. So I changed the query to:

DECLARE @customerId NVARCHAR(1024)
SET @customerId = ' some subscriber id '

SELECT Id, Metadata
FROM Customers
WHERE Metadata.exist('/customerMetadata/subscriber[@Id=sql:variable("@customerId")]') = 1

Happy as I was, I copied it into the “TableAdapter Query Configuration” Wizard of Visual Studio 2005. But I got disappointed again. The query wizard ended with the following messages (see picture below):

XQuery: Unable to resolve sql:variable(‘@customerId’). The variable must be declared as a scalar TSQL variable.

What I tried next was to manually add the variable to the variable collection of that query (see below).

And finally it worked! With some hope on better guidance, I tried the same query in Visual Studio 2008. Unfortunately, this had the same result.