SQL Server 2005, Part III: Magic of Structured Search

You were patient enough to read through my ramblings about why Yukon and InfoPath are a match made in heaven, and how to set up basic interop. But these two articles left you dreaming of more. Well, that was their purpose :-).

Magic begins: Structure-aware query. Full text search is, ahem, much less powerful.

Just imagine: you built your resume system. HR folks can search the records by the first and last name of the candidate. Useful, but far from magical. HR comes to you and says: "hey, we want to search by years of experience!"; next day, the way to search by job title, then, they want to see candidates with EA experience...

Query parameters keep coming every week. You're getting sick of modifying the system; maintenance costs are growing. Programming the system is no longer fun.

Well, that was yesterday.

Today, with Yukon, you can build dynamic queries for your customers on the fly: this involves writing an XPath expression or two. In the next few steps, we'll assume that you have the basic setup completed (if not yet, follow this article).

Start with the goal

Here's what we're trying to build:

Works like this: pick the property you want to search (the list is dynamic); select a value that you want to search that property for; then click Run Query. This performs a few calculations, then calls a web service, and shows the result set. Then, the user can drill down on each record using the Details button.

Why? Where's the magic? HR comes and says "I want another query criteria", and you deliver it by authoring one line of XPath in an InfoPath form. No form template modifications, no web service modifications, no C# to write.

Here's what the end goal looks like at design time:

Going through the building blocks:

1) fieldToSearch dropdown: this is the core of the magic. Here you specify key-value pairs for "what XPath you want searched vs the name of the search". For example, the Job Title search option that you see on the screenshot above maps to a smart-y XPath 

//ns:Emp.JobTitle[contains(.,"%1")]

You can conceptually see that the JobTitle node will be searched for values specified somewhere. Hey, you might ask, where's that printf() syntax coming from?? You'll see an answer soon - we'll be doing a substitution of %1 for a real value coming from the fieldValue node in the web service code, described in detail below.

The key-value pairs for this dropdown would likely come from a secondary data source - an XML file that you'd keep separately, and author using another InfoPath form. Here are some sample values:

2) fieldValue textbox: this allows the user to specify the value to search for. In the example above, they said that they want to search the "Title" field for the word "Manager".

3) Run Query button: has a rule on it, with a few actions - using the Logic Inspector:

Here we're setting the parameters on the secondary data connection that queries a web service; here's a screenshot of its data source:

Why are we hard-coding the select XPath? Just for this example, it's likely that the basic info would be "show me the first and last names, and if I want to drill down, I will". Everything else is dynamic; if you'd like, you can specify selectXPath dynamically in the separate XML file mentioned in step 1.

Now the web service method source code - the beauty is that it's completely generic, and you don't have to change anything in the web service

[WebMethod]
/* criteriaXPath must contain %1. It will be used to substitute in the criteriaValue
* selectXPath must point to one, and only one text node (concat's are fine -
* just make sure that result can be casted to varchar)
* None of the parameters should have a single quote in them: this is just a ,
* you may want to improve this before going to production (escaping).
*/
public XmlDataDocument DoXQuery(string criteriaXPath, string criteriaValue, string selectXPath)
{
    XmlDataDocument result = null;
    string criteria = criteriaXPath.Replace("%1", criteriaValue);

    using (SqlConnection conn = new SqlConnection(connString))
    {
        string command = @"
WITH XMLNAMESPACES (
'http://tempuri.org/Resume' AS ns
)

SELECT
JobCandidateID AS id,
Resume.value('" + selectXPath + @"', 'nvarchar(128)') AS value
FROM " + tableName + @"
WHERE Resume.exist('" + criteria + "') = 1";
       
        SqlDataAdapter myCommand = new SqlDataAdapter(command, conn);
        DataSet ds = new DataSet();
        myCommand.Fill(ds, "JobCandidates");
        result = new XmlDataDocument(ds);
    }
    return result;
}

And you're done!

Alex Weinstein
Program Manager