Passing xml to use as search criteria in a query (optional parameters)

We’ve had to generate a search facility for books on a site. The user types in any number of values and the results are ranked based on how many of the terms are actually found.

The example below has a hard coded XML data, but obviously this would be passed in as a parameter, and as you build this externally you can include as many terms as you need.

DECLARE @xml XML
SET @xml = '<search>
              <term text="and" />
              <term text="the" />
              <term text="princess" />
              <term text="9780719554285" />
            </search>';

/* Use a CTE to load the terms into a table for later joining */
With MatchingTerms  (Term)
AS ( Select node.value('./@text', 'varchar(2000)') AS Term
        FROM @xml.nodes(N'//term') T(node))

SELECT  b.id, 
            b.coverTitle,
            b.ISBN13,
            shortName as [binding],
            'B' as [type],
            count(id) as score
FROM    Book b,
           MatchingTerms x
WHERE
     b.coverTitle like '%' + x.Term + '%'
OR 
     b.ISBN13 = x.Term
GROUP BY 
     b.id,  b.coverTitle,  b.ISBN13, shortName

Specify file name for file from handler

When returning content to a user from a generic handler use the the AddHeader method to specify the filename

context.Response.ContentType = "text/comma-separated-values";
context.Response.AddHeader("content-disposition", "attachment; filename=PrinterDirect.CSV");