Many of the blog entries that I write about are based on questions that customers ask me. One of the areas that customers tend to ask most about are advanced mapping topics. I thought it would be a good idea to take many of these questions and post them as blog entries.
The first in this series is an example of when custom inline XSLT is required.
In this example I received a schema where cities were listed with their name and country as elements. The transformation that needed to take place was to group the output by country and within each country to list all of the cities in that country
The source schema looked like this:
And the destination schema needed to look like this:
And finally the sample input looked like this:
<ns0:Root xmlns:ns0=”http://Testing.SchemaSrc”>
<cities>
<city>
<name>Paris</name>
<country>France</country>
</city>
<city>
<name>Roma</name>
<country>Italia</country>
</city>
<city>
<name>Nice</name>
<country>France</country>
</city>
<city>
<name>Madrid</name>
<country>Espana</country>
</city>
<city>
<name>Milano</name>
<country>Italia</country>
</city>
<city>
<name>Firenze</name>
<country>Italia</country>
</city>
<city>
<name>Napoli</name>
<country>Italia</country>
</city>
<city>
<name>Lyon</name>
<country>France</country>
</city>
<city>
<name>Barcelona</name>
<country>Espana</country>
</city>
</cities>
</ns0:Root>
In order to make this transformation you need to use inline XSLT. There is really no easy way to do this with the functoids provided by BizTalk.
The first thing we need to do is to drag a Script functoid onto the Grid. Then drag a line to the element that we want to create output for. With the inline XSLT functionality we are required to produce output for all of the elements underneath the records our functoid is attached. So, in our example we are responsible for creating all of the content under the countries record.
The map will look like this:
The following XSLT can be cut and paste into the Inline Script Buffer section of the script functoid after the Script Type drop down as been set to Inline XSLT.
To create the output in the format that we want we will start with the following XSLT.
<xsl:variable name=”unique-countries”
select=”//cities/city[not(@country=preceding-sibling::city/@country)]/@country” />
This creates a variable named unique-countries and populates with a list of unique country nodes. Now we need to start the output of our nodes and then loop through the countries. The following code will do that.
1. <countries>
2. <xsl:for-each select=”$unique-countries”>
3. <country name=”{.}”>
4. <xsl:for-each select=”//city[@country=current()]”>
5. <city><xsl:value-of select=”@name” /></city>
6. </xsl:for-each>
7. </country>
8. </xsl:for-each>
9. </countries>
Lets walk through this code.
Lines 1 and 9 create the countries node output and close out the node.
Line 2 creates the loop that will loop through all of the unique countries.
Lines 3 and 7 create the Country node output and close out the node – notice the name attribute. This syntax takes the current node name and writes out the value.
Lines 4 and 6 create the country node output and close out the node. Also line 4 uses the current() function which takes the country of the current node and with the code in line 5 puts the value that is in the source document in the name element and writes it out in the city node.
This code will loop through as many unique countries as are contained in the input document. Then for each unique country that is found the code will loop through all of the city/name nodes outputting their values. When the next country node is current then the code loops though looking for all of the city/name nodes that match
The output looks like this:
<ns0:Root xmlns:ns0=”http://Testing.SchemaDst”>
<countries>
<country name=”France”>
<city>Paris</city>
<city>Nice</city>
<city>Lyon</city>
</country>
<country name=”Italia”>
<city>Roma</city>
<city>Milano</city>
<city>Firenze</city>
<city>Napoli</city>
</country>
<country name=”Espana”>
<city>Madrid</city>
<city>Barcelona</city>
</country>
</countries>
</ns0:Root>