Many applications have the evil feature that asks programmers to create a "Query tool" to allow end users (who don't know SQL or the exact schema of the DB) to create their own queries to look up ...

SQL Server 2005 makes it pretty easy to deal with XML. I'd probably just serialize whatever collection there is on the client side to XML, pass that to a stored procedure, then query against it.