Query XML column in SQL Stored Procedure

Other SQL Server Topics
1

I created a stored procedure to query XML column in SQL. However, it gave me the error "Msg 2219, Level 16, State 1, Procedure sp_GetEngagementRequestBySearch, Line 30 [Batch Start Line 9]
XQuery [dbo.EngagementRequestResponse.RequestXML.exist()]: There is no attribute named '@SearchString' in the type 'text'."

@SearchString varchar(100)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT [RequestName] As Request_ID
 where [RequestXML].exist('/engagement_request//text()[contains(., @SearchString)]') = 1

If I replace @SearchString with a string like "test", it will work. Thanks for your help!

2

please post sample xml. the way you have it wont work as it cannot parse that variable as it is in the quotes.
wither dynamic query or a 2 step query

3

Thanks, I figured it out using following where condition:

where ( Charindex(@SearchString, [RequestXML].query('/engagement_request').value('.','varchar(max)'))>0 )

Basically, there is no need to use the non-typed function text(). Just cast the whole XML into a string and perform the Charindex search is working just fine.