We have a working search feature in our application that takes the user search terms and creates an XML parameter to the SQL stored procedure, allowing the result set to be filtered based on the search parameters. This was done after some research using CROSS APPLY. We now want to expand on this because it does an 'OR' search for all the terms, and we want to offer the ability to do a 'AND' search.
Here is an example of the query in the SP.
CREATE procedure [dbo].[getresources]
select distinct ModuleId,
from resources with (nolock)
CROSS APPLY @xmlTerms.nodes('//SearchTerms/SearchTerm') xmlTerms(i)
WHERE (Title LIKE '%' + xmlTerms.i.value('.', 'VARCHAR(50)') + '%'
Description LIKE '%' + xmlTerms.i.value('.', 'VARCHAR(50)') + '%')
AND ModuleId = @ModuleId
A sample XML would like this:
This works fine but we're finding too many results are returned for several keywords because the CROSS APPLY is an 'OR' search, so we are trying to add an 'AND' option. It will be controlled by the allTerms parameter, which would be 0 for the standard 'OR' and 1 for the 'AND'.
The problem is how to get the query to check the value of the column against the XML parameter value(s). I've tried using a IN (SELECT ...) but kept running into syntax problems due to the conversion of XML to a dataset. Most of the examples online deal with taking XML input and creating records in a database, not searching records based on the XML parameter.
Any thoughts on how this might be possible?