ADR is failing with 0X87D20003 SQl Error

[ogeccut 2]

I have an ADR that failed with 0X87D20003 SQl Error.

I have found this site that has the same error, and its related to a deleted collection or missing collection.

https://sccmnotes.wordpress.com/2019/07/10/automatic-deployment-rule-failing-with-error-0x87d20003/comment-page-1/

I am trying to run a query to identify a missing collection, but getting an error:

declare @ADRName varchar (200) = 'Workstation Updates ADR'
select
   Data.value('(/DeploymentCreationActionXML//CollectionId/node1]', 'nvarchar(max)') CollectionID
 , c.CollectionID
 , c.Name
from RuleActions ra
left join v_Collection c on c.CollectionID = Data.value('(/DeploymentCreationActionXML//CollectionId/node1]', 'nvarchar(max)')
where ra.RuleID = (select AutoDeploymentID from vSMS_AutoDeployments where Name = @ADRName)
and ra.Name = 'DeploymentTemplate'

Msg 2205, Level 16, State 1, Line 7
XQuery [RuleActions.Data.value()]: ")" was expected.

What have i missed in the query? 

I have also checked and all collections are in good state.

Thanks for the help.