DECLARE @DSID NVarchar(50),@XMLPath Nvarchar(100),@RoldId int,@Debug varchar(max)
set @Debug=‘<Roles><Role code=”111″ name=”RoleName”><Persons><Person FName=”FName” LName=”LName” DSID=”564″ Email=”P@email.com” /><Person FName=”1FName” LName=”1LName” DSID=”1234″ Email=”P1@email.com” /></Persons></Role></Roles>’
set @RoldId = 111
if ( (convert(XML,@Debug)).exist(‘(/Roles/Role[@code= sql:variable("@RoldId")])’) =1 )
Begin
SELECT @DSID=(convert(XML,@Debug)).value(‘(/Roles/Role[@code= sql:variable("@RoldId")]/Persons/Person/@DSID)[1]‘,‘nvarchar(50)’)
print @DSID
select * from [Partner] where [ID]=@DSID
End
