IN SQL how to retrieve XML column data based on element

Question

I have the XML inside one of the table columns, here I need to find rows based on 'DESCRIPTION' which is present in the table.

Sample XML column value

<Activity>
  <text value="ID:HELLO, DESCRIPTION:PROJECT112233 , CREDIT:1.83, INTEREST:3.77, TOTAL:5.60" />
</Activity>

I tried following , but its not working

DECLARE @Description varchar(100) 

SET @Description='PROJECT112233'

SELECT * FROM DataLogs WHERE 
ActivityDescription.exist('"+ @Description +"') = 1

Sample Data Build

create table DataLogs
(
    id int primary key,
    created_date date,
    ADescription XML
  )

INSERT into DataLogs 
values (1,'2024-04-16','<Activity><text value="ID:HELLO, DESCRIPTION:PROJECT112233 , CREDIT:1.83, INTEREST:3.77, TOTAL:5.60"'/></Activity>');
insert into DataLogs values (2,'2024-04-12','<Activity>
  <text value="ID:LO, DESCRIPTION:ECT112233 , CREDIT:0.83, INTEREST:0.77, TOTAL:1.60" />
</Activity>');
insert into DataLogs values (3,'2024-04-10','<Activity>
  <text value="ID:Junk, DESCRIPTION:CT33 , CREDIT:1.00, INTEREST:2.00, TOTAL:3.00" />
</Activity>');
insert into DataLogs values (4,'2024-04-01','<Activity>
  <text value="ID:Jk, DESCRIPTION:MT2313 , CREDIT:2.00, INTEREST:4.00, TOTAL:6.00" />
</Activity>');

Answer 1

One way to do this would be to make use of the sql:variable() function in XQuery, e.g.:

declare @Description nvarchar(100) = N'DESCRIPTION:PROJECT112233';
select *
from DataLogs
where ADescription.exist('/Activity/text[contains(@value, sql:variable("@Description"))]') = 1;

Returns the output:

id	created_date	ADescription
1	2024-04-16	<Activity><text value="ID:HELLO, DESCRIPTION:PROJECT112233 , CREDIT:1.83, INTEREST:3.77, TOTAL:5.60" /></Activity>