Need the XSLT / manner in which we can combine two messages

Hi,

I am trying to combine two messages (took some help from Sandro's blogs) for merging data. My head is still not around XSLT and am not able to get the desired result. Can anyone suggest me the way or in particular the probable xslt template that I need to put across. Thanks in advance.

Message 1
=========
<EmployeeLookup>
  <EmployeeQuery>
    <MsgId>XXX</MsgId>
    <LookupValues>
      <LookupValue>
        <Name>SSN</Name>
        <Value>123456</Value>
      </LookupValue>
      <LookupValue>
        <Name>DrivingLicense</Name>
        <Value>AA-234-123</Value>
      </LookupValue>
    </LookupValues>
  </EmployeeQuery>
  <EmployeeQuery>
    <MsgId>YYY</MsgId>
    <LookupValues>
      <LookupValue>
        <Name>SSN</Name>
        <Value>456677</Value>
      </LookupValue>
    </LookupValues>
  </EmployeeQuery>
  <EmployeeQuery>
    <MsgId>ZZZ</MsgId>
    <LookupValues>
      <LookupValue>
        <Name>SSN</Name>
        <Value>fakessn</Value>
      </LookupValue>
    </LookupValues>
  </EmployeeQuery>
</EmployeeLookup>

Message 2
=========

<KnownEmployees>
  <Employee>
    <eno>101</eno>
    <SSN>123456</SSN>
    <DrivingLicense>AA-234-123</DrivingLicense>
  </Employee>
  <Employee>
    <eno>102</eno>
    <SSN>456677</SSN>
    <DrivingLicense>BB-494-432</DrivingLicense>
  </Employee>
</KnownEmployees>
Message 3
=========
<CombinedResult>
  <Employee>
    <MsgId>XXX</MsgId>
    <RetrievedIds>
      <Identifier>
        <Name>eno</Name>
        <Value>101</Value>
      </Identifier>
    </RetrievedIds>
  </Employee>
  <Employee>
    <MsgId>YYY</MsgId>
    <RetrievedIds>
      <Identifier>
        <Name>eno</Name>
        <Value>102</Value>
      </Identifier>
    </RetrievedIds>
  </Employee>
  <Employee>
    <MsgId>ZZZ</MsgId>
    <RetrievedIds />
  </Employee>
</CombinedResult>
I am of course using a map which is created inside an orch so that we can have multiple sources and writing to destination.
September 7th, 2015 3:13pm

Hi Praveen,

I think you can do this using BizTalk mapper and don't need XSLT. Biztalk mapper can accept two source messages and create one output. When you normally create a BizTalk mapper by right click project and add a new file you don't get this feature. Create a dummy orchestration and drag a transform shape into the orchestration. Open transform and create new, here you can specifiy two input messages as source. Later you can delete orchestration if you don't want and keep the map working any where.

Cheers

Free Windows Admin Tool Kit Click here and download it now
September 7th, 2015 6:51pm
Hi JB, I guess you missed my last line. :) I was specifically looking for the pattern of using a xslt template (scripting functoid) for picking up values of 2nd msg based on keys of 1st msg.
September 8th, 2015 2:38am

Hi Praveen,

have one doubt, how do you relate the Employee, EmployeeQuery ?

For example i am using the below condition to relate the tow records to map to destination.

XML Input

<ns0:Root xmlns:ns0="http://schemas.microsoft.com/BizTalk/2003/aggschema">
	<InputMessagePart_0>
		<EmployeeLookup>
			<EmployeeQuery>
				<MsgId>MsgId_0</MsgId>
				<LookupValues>
					<LookupValue>
						<Name>Name_0</Name>
						<Value>10</Value>
					</LookupValue>
				</LookupValues>
			</EmployeeQuery>
			<EmployeeQuery>
				<MsgId>MsgId_1</MsgId>
				<LookupValues>
					<LookupValue>
						<Name>Name_1</Name>
						<Value>20</Value>
					</LookupValue>
				</LookupValues>
			</EmployeeQuery>
			<EmployeeQuery>
				<MsgId>MsgId_2</MsgId>
				<LookupValues>
					<LookupValue>
						<Name>Name_2</Name>
						<Value>30</Value>
					</LookupValue>
				</LookupValues>
			</EmployeeQuery>
		</EmployeeLookup>
	</InputMessagePart_0>
	<InputMessagePart_1>
		<KnownEmployees>
			<Employee>
				<eno>1</eno>
				<SSN>10</SSN>
				<DrivingLicense>DrivingLicense_0</DrivingLicense>
			</Employee>		
			<Employee>
				<eno>2</eno>
				<SSN>20</SSN>
				<DrivingLicense>DrivingLicense_1</DrivingLicense>
			</Employee>
			<Employee>
				<eno>3</eno>
				<SSN>30</SSN>
				<DrivingLicense>DrivingLicense_2</DrivingLicense>
			</Employee>
		</KnownEmployees>
	</InputMessagePart_1>
</ns0:Root>

Map Output

<CombinedResult>
	<Employee>
		<MsgId>MsgId_0</MsgId>
		<RetrievedIds>
			<Identifier>
				<Name>Name_0</Name>
				<Value>10</Value>
			</Identifier>
		</RetrievedIds>
	</Employee>
	<Employee>
		<MsgId>MsgId_1</MsgId>
		<RetrievedIds>
			<Identifier>
				<Name>Name_1</Name>
				<Value>20</Value>
			</Identifier>
		</RetrievedIds>
	</Employee>
	<Employee>
		<MsgId>MsgId_2</MsgId>
		<RetrievedIds>
			<Identifier>
				<Name>Name_2</Name>
				<Value>30</Value>
			</Identifier>
		</RetrievedIds>
	</Employee>
</CombinedResult>

XSLT for Mapping.

<xsl:template name="T">
		<CombinedResult>
			<xsl:for-each select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_0' and namespace-uri()='']/*[local-name()='EmployeeLookup' and namespace-uri()='']/*[local-name()='EmployeeQuery' and namespace-uri()='']">
				<xsl:variable name="PosEmployeeQuery" select="position()"/>
				<xsl:variable name="MsgId" select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_0' and namespace-uri()='']/*[local-name()='EmployeeLookup' and namespace-uri()='']/*[local-name()='EmployeeQuery' and namespace-uri()=''][$PosEmployeeQuery]/*[local-name()='MsgId' and namespace-uri()='']"/>
				<xsl:variable name="Name" select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_0' and namespace-uri()='']/*[local-name()='EmployeeLookup' and namespace-uri()='']/*[local-name()='EmployeeQuery' and namespace-uri()=''][$PosEmployeeQuery]/*[local-name()='LookupValues' and namespace-uri()='']/*[local-name()='LookupValue' and namespace-uri()='']/*[local-name()='Name' and namespace-uri()='']"/>
				<xsl:variable name="Value" select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_0' and namespace-uri()='']/*[local-name()='EmployeeLookup' and namespace-uri()='']/*[local-name()='EmployeeQuery' and namespace-uri()=''][$PosEmployeeQuery]/*[local-name()='LookupValues' and namespace-uri()='']/*[local-name()='LookupValue' and namespace-uri()='']/*[local-name()='Value' and namespace-uri()='']"/>

				<xsl:for-each select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='KnownEmployees' and namespace-uri()='']/*[local-name()='Employee' and namespace-uri()='']">
					<xsl:variable name="PosEmployee" select="position()"/>
					<xsl:variable name="eno" select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='KnownEmployees' and namespace-uri()='']/*[local-name()='Employee' and namespace-uri()=''][$PosEmployee]/*[local-name()='eno' and namespace-uri()='']"/>
					<xsl:variable name="SSN" select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='KnownEmployees' and namespace-uri()='']/*[local-name()='Employee' and namespace-uri()=''][$PosEmployee]/*[local-name()='SSN' and namespace-uri()='']"/>
					<xsl:variable name="DrivingLicense" select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='KnownEmployees' and namespace-uri()='']/*[local-name()='Employee' and namespace-uri()=''][$PosEmployee]/*[local-name()='DrivingLicense' and namespace-uri()='']"/>

					<xsl:if test="$SSN=$Value">
						<Employee>
							<MsgId>
								<xsl:value-of select="$MsgId"/>
							</MsgId>
							<RetrievedIds>
								<Identifier>
									<Name>
										<xsl:value-of select="$Name"/>
									</Name>
									<Value>
										<xsl:value-of select="$SSN"/>
									</Value>
								</Identifier>
							</RetrievedIds>
						</Employee>
					</xsl:if>
				</xsl:for-each>
			</xsl:for-each>
		</CombinedResult>
	</xsl:template>

Attachments for the same Click here

You can download the file named "SepTestMapping"

There are other ways also to do, i tried with this.

Thanks, SMSVikasK

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 6:13am

Hi Vikas,

Thanks, I will see if I can use it as an interim solution.

The idea is to lookup the eno(call it primary key) based on any of the other surrogate keys (SSN, Driving License). There is no limit to the surrogate keys and it can be any of them, which we can send to a service for lookup. Hence, the name-value pair structure.

The response will be a single record per employee with the eno with all the surrogate keys. Now, when picking up.. for each lookup request, we need to compare the surrogate key(s) with the keys in the result and pickup the eno.

The combined schema will have all employees with the corresponding eno. Hope this puts the problem statement more clearly.

September 8th, 2015 9:28am

Guys,

Got the solution after some amount of hit and trail. Again, had to resort to creating an XSLT template. You would need 2 scripting functoids.. one for creating the xsl:key(without any links) and other for the actual creation of destination nodes (so mapped only to destination, no links to source). This is a pattern mentioned by Sandro in his mapping book.

Scripting Functoid 1 (Inline XSLT Call Template)

<xsl:key name="LookupResp" match="//Employee" use="concat(local-name(*),"|", *)" />

Scripting 2 (Inline XSLT Call Template)

<xsl:template name="GetUnique">
  <xsl:if test="count(key('LookupResp',concat(LookupValues/LookupValue/Name,'|',LookupValues/LookupValue/Value))) &gt; 0">
    <Identifier>
      <Name>
        <xsl:value-of select="&quot;eno&quot;"/>
      </Name>
      <Value>
        <xsl:value-of select="key('LookupResp',concat(LookupValues/LookupValue/Name,'|',LookupValues/LookupValue/Value))/eno"/>        
      </Value>
    </Identifier>
  </xsl:if>
</xsl:template>

There is a looping functoid LookupValues (schema1) to RetrievedIds(schema 3).
FYI.. Spent so much time testing with node() instead of *.. know the difference. Also, the // can be replaced with a proper path for better performance.
Thanks for all the suggestions.

One further requirement I still have is.. if I want to copy all the Retrieved Fields and not just eno.. how to do that?


Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 9:00am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics