Playing with SharePointPSSearch SQL Syntax

I've been working with a technical customer lately and they wanted me to demonstrate what they could do with the Sharepoint Search engine. I explained that you could easily consume the portal search service just as any other web service (as Bil describes nicely here) and make customized searches.

They wanted me to make a demo as a proof-of-concept so I created a portal area on their test server and added what they needed to experiment for themselves in addition to a small sample. The cool thing about this is that you really can experiment with the SharepointPSSearch SQL Syntax right in the browser.

The setup was really basic; one Form (get dwp here)webpart with the default querypacket xml displayed inline, one DataView webpart consuming the search.asmx service based on the form input (get dwp here), and a Sample webpart displaying a predefined call to QueryEx. The sample was a little bit tricky to get working. I needed to call QueryEx to get DataSet formatted xml returned. Frontpage automatically applies XSL to the DataSet so that eases the display logic. Additionally the SQL Query part of the xml is usually embedded in a CDATA section. Because the DWP itself is embedded in a CDATA section this causes problems (embedded CDATA sections is not allowed).

The solution was to htmlencode the queryXml part of the input parameter to the webservice. To accomplish this you'll need to connect to the service as DataView in FrontPage, then add a dummy value for the queryXml parameter. Close the dialog and re-open it. Click"Manually edit properties" and paste in the html encoded querypacket inside the queryXml element.

If you download the dwps and import them to your site remember to edit the XMLQueryResult.dwp file and insert your credentials and URIs first. I had some problems making the DataView webpart connect with windows authentication so the sample runs with basic (let me know if you see a reason for this). Also remember to set up webpart connections with frontpage (so you can set "modify" target webpart and not "filter" target webpart values).

The cool thing about this is that you (or technical customers) can experiment with the full potential of the Search Engine and see new possibilities for custom views and queries. I've included a screenshot of the Search Demo page here (click for large image):