Wednesday, 25 March 2009

Hi all

Someone at the online general BizTalk forum asked a question about combining two messages in a map. Now, he all ready knew about creating the map from inside an orchestration, but let me just quickly summon up for those not knowing this. If you have two messages inside an orchestration that you need to merge into one message in a map, what you do is that you drag a transformation shape into your orchestration like this:

DualInputOrchestration

In my example, I have a parallel convoy to get the two input messages into my orchestration. I then have two different ways of combining the two input messages into one output, and each is then output.

Anyway, after the transform shape is dragged onto the orchestration designer, you double-click on it to choose input and output messages like this:

DualInputCreateMap

You can add as many source messages as you want – I have chosen two messages. Make sure the checkbox at the bottom is selected. Then click “OK” and the mapper will open up. It will have created an input schema for you, which is basically a root node that wraps the selected source messages. At runtime, the orchestration engine will take your messages and wrap them to match this schema and use that as input for the map.

In my case, I have these two schemas:

DualInputSchemaHouseBill 

and

DualInputSchemaWayBill

My output schema looks like this:

DualInputSchemaOutput

The automatically generated map looks like this:

DualInputGeneratedMap

As you can see, the destination schema is just like my output schema, but the input schema wraps my two input schemas into one schema.

So… I have just briefly explained how two create the map that can combine two messages into one. Now for the functionality inside the map.

Most maps like this can be mapped like any other complex input schema. But sometimes you need to somehow merge elements inside the source messages into one element/record in the destination. This automatically becomes different, because the values will appear in different parts of the input tree.

The requirement that was expressed by the person asking the question in the online forum was that these two inputs:

DualInputSchemaHouseBillExample

and

DualInputSchemaWayBillExample

and combine them into this:

DualInputSchemaOutputExample

So basically, there is a key that is needed to combine records in the two inputs. My schemas above are my own schemas that roughly look like the schema that was in use in the forum.

My first map that will solve the given problem looks like this:

DualInputMapFunctoids

Quite simple, actually. I use the looping functoid to create the right number of output elements, and I use the iteration and index functoids to get the corresponding values from the WayBill part of the source schema. The index funtoid can take a lot of inputs. In my case the path to the element is always the first until the vey last step, where I need to use the output of the iteration functoid. So I have only two inputs: The element that loops and the index of the parent of this element because that is the only place where I need to go to a specific element.

This works very nicely, but it has one serious drawback (and a minor one, which I will get back to later): It requires that the elements appear in the exact same order in both inputs. If this restriction can be proven valid, then this is my favorite solution, since I am a fan of using the built-in functoids over scripting functoids and custom XSLT if at all possible. I didn’t ask the person who had the issue if this restriction is valid, but thought I’d try another approach that will work around this just in case. This requires some XSLT, unfortunately, and the map looks like this:

DualInputMapXSLT

Quite simple, really :-) The scripting functoid takes care of the job for me. It is an “Inline XSLT Call Template” functoid and the script goes like this:

<xsl:template name="BuildOutput">
<xsl:param name="ID" />
<xsl:element name="Output">
<xsl:element name="Number"><xsl:value-of select="$ID" /></xsl:element>
<xsl:element name="OriginPortId"><xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_0' and namespace-uri()='']/*[local-name()='HousebillRoot' and namespace-uri()='http://DualInput.DualSchemaHouseBillInput']/*[local-name()='HouseBillsNode' and namespace-uri()=''][HouseBillNo = $ID]/*[local-name()='OriginPortId' and namespace-uri()='']" /></xsl:element>
<xsl:element name="ShippingAddress"><xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='WayBillRoot' and namespace-uri()='http://DualInput.DualSchemaWayBillInput']/*[local-name()='WayBillInfo' and namespace-uri()=''][WayBillNo = $ID]/*[local-name()='ShippingAddress' and namespace-uri()='']" /></xsl:element>
<xsl:element name="ContainerAddress"><xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='WayBillRoot' and namespace-uri()='http://DualInput.DualSchemaWayBillInput']/*[local-name()='WayBillInfo' and namespace-uri()=''][WayBillNo = $ID]/*[local-name()='ContainerAddress' and namespace-uri()='']" /></xsl:element>
</xsl:element>
</xsl:template>

Now this looks complex, but really it isn’t. Let me try to shorten it for you to be more readable:

<xsl:template name="BuildOutput">
<xsl:param name="ID" />
<xsl:element name="Output">
<xsl:element name="Number"><xsl:value-of select="$ID" /></xsl:element>
<xsl:element name="OriginPortId"><xsl:value-of select="XXX/*[local-name()='HouseBillsNode' and namespace-uri()=''][HouseBillNo = $ID]/*[local-name()='OriginPortId' and namespace-uri()='']" /></xsl:element>
<xsl:element name="ShippingAddress"><xsl:value-of select="YYY/*[local-name()='WayBillInfo' and namespace-uri()=''][WayBillNo = $ID]/*[local-name()='ShippingAddress' and namespace-uri()='']" /></xsl:element>
<xsl:element name="ContainerAddress"><xsl:value-of select="YYY/*[local-name()='WayBillInfo' and namespace-uri()=''][WayBillNo = $ID]/*[local-name()='ContainerAddress' and namespace-uri()='']" /></xsl:element>
</xsl:element>
</xsl:template>

Here XXX is the XPath from the root node down to the HouseBillsNode node and YYY is the XPath from the root node down to the WayBillInfo node.

Basically, the script is fired by the map for each HouseBillNo element that appears (3 in my example) and the script will create an Output element with the HousebillNo value and i will then use the number to look up the values that correspond to the key in the other parts of the input.

There are some drawbacks to this solution as well, and I will just try to summon up the drawbacks here:

Drawbacks for first maps

  1. If the elements do not appear in the exact same order in both inputs, the map will fail.

Drawbacks for the second map

  1. The script has not been adjusted to handle optional fields. So it will create the output fields no matter if the input fields exist in the source.

Drawbacks for both maps

  1. If the HouseBill input has more elements than the other, then the output will be missing values for the elements that would get there values form the second input.
  2. If the HouseBill input has fewer elements than the other, then the output will simply not have records corresponding to these extra elements in the WayBill input.
  3. Both scenarios can be handled in the XSLT, naturally, if needed.

There are probably other drawbacks – most of them related to the fact that I was too lazy to handle all exceptions that might occur. But you should get the idea anyway :-)

The solution can be found here

.

Hope this helps some one…

--
eliasen

Wednesday, 25 March 2009 23:26:40 (Romance Standard Time, UTC+01:00)  #    Comments [5]  | 
Thursday, 26 March 2009 00:43:22 (Romance Standard Time, UTC+01:00)
A great explanation of a many to one mapping where the values in the output message are a combination of the two input messages.
How would you handle the many to one mapping scenario where The values in the output message are an aggregation of the two input messages with one value coming from only one input of one message? See http://connectedpawns.wordpress.com/2009/02/03/many-to-one-mappings/ and http://connectedpawns.wordpress.com/2009/03/08/many-to-one-mappings-using-the-table-looping-functoid/
Tuesday, 31 March 2009 23:54:47 (Romance Daylight Time, UTC+02:00)
Hi Mark

I am not sure what you are asking... You seem to have a fine and working solution to your problem, so what is the issue? If you are asking if I ahve a better solution than you have come up with yourself, then try sending me an email with a schema for your input, a schema for your output, an input example and the output you want.

--
eliasen
Friday, 06 August 2010 18:05:07 (Romance Daylight Time, UTC+02:00)
Hi Eliasen,

Your solutions looks great!
Solution one works perfectlly for me, however, I need to use solution two as well.

For solution two, it seems not working.

Below is my two messages:
Message 1:

<pARDetailsGetResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<StoredProcedureResultSet0>

<StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARDetailsGet">
<NettingARDetailId>941</NettingARDetailId>
<InvoiceNum>30139887</InvoiceNum>
</StoredProcedureResultSet0>

</StoredProcedureResultSet0>
</pARDetailsGetResponse>

Message 2:

<pNettingARiPowerDetailsGetResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<StoredProcedureResultSet0>

<StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet">
<InvoiceNum>30139887</InvoiceNum>
<IPowerInvoiceDate>2010-05-12T00:00:00Z</IPowerInvoiceDate>
<IPowerBillBalAmount>5811.2100</IPowerBillBalAmount>
<IPowerLocalBalAmount>5811.2100</IPowerLocalBalAmount>
</StoredProcedureResultSet0>

</StoredProcedureResultSet0>

<ReturnValue>0</ReturnValue></pNettingARiPowerDetailsGetResponse>

Inline Xslt template code:
<xsl:template name="BuildInvoice">
<xsl:param name="ID" />

<xsl:element name="Invoice">

<xsl:element name="InvoiceNum"><xsl:value-of select="$ID" /></xsl:element>

<xsl:element name="NettingARDetailId">
<xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_0' and namespace-uri()='']/*[local-name()='pNettingARDetailsGetResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARDetailsGet'][InvoiceNum = $ID]/*[local-name()='NettingARDetailId' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARDetailsGet']" />
</xsl:element>

<xsl:element name="iPowerInvoiceDate">
<xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='pNettingARiPowerDetailsGetResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet'][InvoiceNum = $ID]/*[local-name()='IPowerInvoiceDate' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet']" />
</xsl:element>

<xsl:element name="iPowerAmount">
<xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='pNettingARiPowerDetailsGetResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet'][InvoiceNum = $ID]/*[local-name()='IPowerBillBalAmount' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet']" />
</xsl:element>

<xsl:element name="iPowerLocalAmount">
<xsl:value-of select="/*[local-name()='Root' and namespace-uri()='http://schemas.microsoft.com/BizTalk/2003/aggschema']/*[local-name()='InputMessagePart_1' and namespace-uri()='']/*[local-name()='pNettingARiPowerDetailsGetResponse' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet'][InvoiceNum = $ID]/*[local-name()='IPowerLocalBalAmount' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/pNettingARiPowerDetailsGet']" />
</xsl:element>

</xsl:element>
</xsl:template>

The result:
No value has been selected out except InvoiceNum($ID) itself.

Thank you very much!
Ben
Friday, 06 August 2010 19:59:47 (Romance Daylight Time, UTC+02:00)
Hi Ben

Troubleshooting this would be easier for me, i you would send me:
1. The two schemas in question
2. Two instances
3. The map as you have it now

Thanks.

--
eliasen
Friday, 13 August 2010 20:19:26 (Romance Daylight Time, UTC+02:00)
Hi Eliasen,

I have sent you an email with the schemas, instances and the map.
Please let me know if you did not receive it.

Thanks a lot!
Ben
All comments require the approval of the site owner before being displayed.
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, sup, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview

Theme design by Jelle Druyts