Querying XML with Input Param

Question

Currently i am using this query to get results :

Select *
From Staff 
Where StaInsurance.exist('/COLInsuranceCompany/DOInsuranceCompany[Name/text()[. = "Apollo Life Assurance"]]') = 1

How can i use a Input Parameter instead of hardcoded string in the query, below did not work :

declare @IInsurance nvarchar(200)
set @IInsurance = 'Apollo Life Assurance'

Select *
From Staff 
Where StaInsurance.exist('(/COLInsuranceCompany/DOInsuranceCompany/Name[id=(sql:variable("@IInsurance"))])[1]') = 1

Backs · Accepted Answer · 2014-01-23 05:51:23Z

1

declare @IInsurance nvarchar(200)
set @IInsurance = 'Apollo Life Assurance'

Select *
From Staff 
Where StaInsurance.exist('/COLInsuranceCompany/DOInsuranceCompany/Name[text() = sql:variable("@IInsurance")]') = 1

edited Jan 23, 2014 at 5:51

answered Jan 23, 2014 at 3:02

Backs

25k5 gold badges66 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2561997 Over a year ago

Thanks it worked after removing the '/' from the last

Backs Over a year ago

@user2561997 yes, a small mistake

Collectives™ on Stack Overflow

Querying XML with Input Param

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related