Using SharePoint Designer we now integrate this table as an External List in SharePoint. Many other SharePoint bloggers have written or done presentations about this concept, for example: Ingo Karstein (Link)
Next step is now creating a profile page to show the search results from the external data in SharePoint Search. This is also a topic many other bloggers written about. Just see Corey Roth blog post: Link
Doing all this we get a search result looking like this:
….not really nice.
To Customize the Search Result using SharePoint Designer and XSLT follow the steps described by Laura Rogers (Link). If you do this with the external contend source you get something looking like this:
The point is there is no field like “Task” or “Date” as in the source SQL Table.
All this information is packed together in the field “hithighlightedsummary”. So first we have put this field to the table . You can do this via drag & drop. The result is similar like this:
Now click “Edit Formula….”in the context menu
You get something like this:
To filter special contend out of the “hithighlightedsummary” field just you Xpath. For example we want to filter out the “Task” field this Xpath expression will work (of cause only for my demo here): substring-before(substring-after(hithighlightedsummary, 'http://intranet/bcs'), 'miclas')
The result is showen in the Preview Section in the edit dialogbox:
With this Xpath query, some other sorting and design changes we are able to get a Data Source View looking like this:
And with the steps described by Laura Rogers (Link) we are now able to create a Search Result looking like this: