While working on my first BizTalk 2006 project I came across the need to enrich messages as they were routed through BizTalk. I knew that a Map would be the logical approach because most fields could be copied through directly. Based on some other properties a database lookup should be performed and the resulting data structure is to be added to the destination message. The structure of this data is complex; an hierarchy with multiple, repeating elements.
After trying all sorts of things covering Database Lookup, and all sorts of Scripting functoids configurations and searching the net, I finally came across this post. http://www.infusionblogs.com/blogs/syd/archive/2006/05/17/480.aspx. It nearly describes the problem I tried to solve and sure enough, within 5 minutes I had a working solution.
I made a custom .NET assembly with a class that has a public method to lookup the extra information based on the source message properties. This method takes several (in my case) string parameters and returns a string that contains the Xml sub structure thats to be included in the destination message. Note that the string returned is the OuterXml of the DocumentElement of the XmlDocument. If you return the OuterXml of the XmlDocument you’ll include a <?xml?> PI and the map doesn’t like that (with good reason).
public class DataLookup
public string Lookup(string param1, string param2)
XmlDocument xmlDoc = new XmlDocument();
// load xml document…
Then I created a map in VS.NET, selected the source and destination schemas and placed two Scripting Functoids on the map-surface. The first scripting functoid has its inputs connected to the required input properties from the source schema and the output is connected to the second scripting functoid and is configured to call out to my .NET assembly. The second scripting functoid is configured as an Xslt call template (code is shown below) and its output connects to the root element in the destination schema where the additional information has to go.
<xsl:param name=”data” />
<xsl:value-of disable-output-escaping=”yes” select=”$data” />
So, during a transformation, the source properties are fed into my custom class’ method, a database lookup is performed and an xml document is created which is returned as a string. This Xml as string is then passed into the xslt named template and injected into the destination message at the specified root element.
Source Properties => (picture of two script functoids) => Destination Xml
It seems a bit strange that you have to convert Xml into a string (to be converted into Xml again by the Xslt template) in an xml technology-based BizTalk map. But hey, it works…