XQuery [proposal.Data.value（）]：'@'附近有语法错误。 ']' 是必须的

I have a query as below was not able to pass the parameter to the xml column

我有一个查询,如下所示无法将参数传递给xml列

DECLARE @ConCatenateString NVARCHAR(MAX); 
DECLARE @QuestionNames varchar(50) =@QuestionName;

SELECT 
    @ConCatenateString = COALESCE(@ConCatenateString + ' ', '') + P2.ApprovalNumber
FROM 
    (SELECT DISTINCT  
         ISNULL(pp.Data.value('(/*/Answers/AnswersList/Entry[@key="[sql:variable("@QuestionNames")]"]/value)[1]', 'nvarchar(max)'),'') AS ApprovalNumber 
     FROM 
         proposal AS pp      
     WHERE
         pp.ProposalId = @ProposalId 
         AND pp.productid = @ProductId
         AND (pp.proposalstatusid = '4' -- declined
              OR pp.proposalstatusid = '1' -- approved
             )
         AND pp.[starteffectivedate]>= '2017-01-01 00:00:00' 
         AND pp.[starteffectivedate] <= '2017-12-31 23:59:59') P2

Error :

Msg 9303, Level 16, State 1, Procedure Concatenate ApprovalNumber, Line 15
XQuery [proposal.Data.value ()]: There is a syntax error near '@'. ']' is required.

Msg 9303,Level 16,State 1,Procedure Concatenate ApprovalNumber,Line 15 XQuery [proposal.Data.value()]:'@'附近有语法错误。 ']' 是必须的。

1 个解决方案

#1

You have some extra qoutes and [] around sql:variable()...

你有一些额外的qoutes和[]围绕sql:variable()...

This works

DECLARE @xml XML='<root><a test="a">A</a><a test="b">B</a></root>';
DECLARE @SearchFor NVARCHAR(10)='a';
SELECT @xml.value(N'(/root/a[@test=sql:variable("@SearchFor")]/text())[1]','nvarchar(max)')

Try to change this to

尝试将此更改为

DECLARE @xml XML='<root><a test="a">A</a><a test="b">B</a></root>';
DECLARE @SearchFor NVARCHAR(10)='a';
SELECT @xml.value(N'(/root/a[@test="sql:variable("@SearchFor")"]/text())[1]','nvarchar(max)')

... and you get the same error you've mentioned

......你得到了与你提到的相同的错误

#1