Valid SQL won't validate

Data Abstract
1

Why won’t this validate?

SELECT Part.Id
FROM Part INNER JOIN
(SELECT t.value(’.’,‘nvarchar(200)’) as Token FROM :Tokens.nodes(’/tokens/token’) AS tokens(t)) TokensTable
ON Part.Description LIKE ‘%’ + TokensTable.Token + ‘%’

I think it’s objecting to the XML logic but I have no idea why.
The SQL is perfectly valid - will it still execute even though validation fails?

2

And the answer is no, it doesn’t work. I get this error:

An exception was raised on the server: Cannot find item “TOKENS.NODES” in collection of type TDAParamCollection.

Please advise as this is a total show-stopper for me.

3

Seems I can work around the error by copying the XML parameters into a local variable and then using that. This allows it to execute but it still won’t validate.

So there are two problems here. Firstly the parameter system can’t cope with a parameter which doesn’t have a space after it, such as “:Tokens.nodes”. Secondly there’s still a spurious validation error in the schema modeller.

4

will it work if you replace

 :Tokens.nodes('/tokens/token')

with

 (:Tokens).nodes('/tokens/token')

?

5

SQL Server doesn’t like that - at least if I try it in a Management Studio query it fails.

6

try to manually replace parameter name from Tokens.nodes with Tokens in DASM