Thursday, December 16, 2010

BizTalk: combing two xml message into one

I ran into the situation where I need to combine two messages into one while developing a BizTalk App for Active Directory Integration.
I used SQL typedpooling to get updates of user information from datebase which contains userID and its Manager's ID as well as some other basic user information (comes from Epicor E4SE), since the Active Directory Adapter I was testing only support using DSN to update manager field of AD user, I have to run a AD query first to the the manager's distinguishedName from AD thus the need to combine them into the final Active Update schema.

Inside BizTalk, you select two messages as input and one destination schema as output in a map, let's say the final input is as below:

 <root>  
 <input1>  
 <ManagerID>ABC02</ManagerID>  
 </Input1>  
 <input2>  
 <ad>  
 <filtermatch ObjectPath="LDAP://CN=B\, Miss,OU=Finance,DC=subs,DC=mycompany,DC=com">  
 <property Name="sAMAccountName" Value="ABC01" />  
 <property Name="distinguishedName" Value="CN=B\, Miss,OU=Finance,DC=subs,DC=mycompany,DC=com" />  
 </FilterMatch>  
 <filtermatch ObjectPath="LDAP://CN=B\, Sir,OU=IT Dept,ODC=subs,DC=mycompany,DC=com">  
 <property Name="sAMAccountName" Value="ABC02" />  
 <property Name="distinguishedName" Value="CN=A\, Sir,OU=IT Dept,DC=subs,DC=mycompany,DC=com" />  
 </FilterMatch>  
 </AD>  
 </Input2>  
 </Root>  

the final custom xsl will be as below:

 <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">  
 <xsl:output method="xml"/>  
 <xsl:template match="/">  
 <xsl:apply-templates />   
 </xsl:template>  
 <xsl:template match="/">  
 <dsn>  
 <xsl:variable name="Manager" select="Root/Input1/ManagerID/text()" />  
 <xsl:value-of select="Root/Input2/AD/FilterMatch[Property[@Name='sAMAccountName' and @Value=$Manager]][1]/Property[@Name='distinguishedName']/@Value" />  
 </DSN>  
 </xsl:template>  
 </xsl:stylesheet>  

Please note that normally the AD query will only returns 1 record, the reason I put more than one here is to experiment a bit and try to think of the way to handle choosing the right node based on the 'Master' xml input.

The key here is to use a xsl:variable to hold the Key field and use it in the xpath for matching up Xml Node.

So far I have managed to integrate Active Directory, our ERP system (Epicor E4SE) and infopath form together to achieve the 'single point of truth' for user records in a pilot. will write more about details sometime later.