Using XSLT to map from many-to-one

This blog is a just a lesson on how to use XSLT to append many rows into one output node using a map with a XSLT Scripting Functiod.

The source message looked something like the one below, where the Line nodes are the repeated rows that are required to be append to one string value.

<SitaBDS xmlns=”http://ECL.Online.BT.JobBooking.SITAAirNZBDS_FF”>
<BagageClaim xmlns=””>
<DS>
      <Lines>
<Field>This is the first line.</Field>
</Lines>
<Lines>
<Field>This is the second line.</Field>
</Lines>
<Lines>
<Field>This is the third line.</Field>
</Lines>
    </DS>
</BagageClaim>
</SitaBDS>

The final output should look like this:

<ns0:Root xmlns:ns0=”http://FFSchema.Schema1″><Field>This is the first line. This is the second line. This is the third line. </Field></ns0:Root>

Below are the steps I normally follow to work out the syntax of the XSL to be placed inside an Inline XSLT Scripting Functiod.

1. Create a BizTalk map for  the source and destination schemas and add a link between the root nodes as shown below.

I initially use this this technique of linking the root elements together to obtain the correct namespaces and a template XSL code to start from.

2. Click on Validate the map to get Visual Studio to generate the XSLT mapping file. This will create the hyperlinks to the generated file as shown.

3. Clicking on the link to the output file of the XSLT will open the file in Visual Studio. Right click on the page and select View Source and take a copy of the code.

4. In Visual Studio add a new XSLT file to your project and replace the sample XSL with the generated XSL from the above step. It should look something like the code below. This method of pasting the generated XSL code provides the required namespaces  and  aliases to use.

   1: <?xml version="1.0" encoding="utf-16"?>

   2: <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"

   3:                 xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"

   4:                 exclude-result-prefixes="msxsl var s0" version="1.0"

   5:                 xmlns:ns0="http://FFSchema.Schema1"

   6:                 xmlns:s0="http://FFSchema.FlatFileSchema1">

   7:   <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />

   8:   <xsl:template match="/">

   9:     <xsl:apply-templates select="/s0:SitaBDS" />

  10:   </xsl:template>

  11:   <xsl:template match="/s0:SitaBDS">

  12:     <ns0:Root>

  13:       <xsl:value-of select="./text()" />

  14:     </ns0:Root>

  15:   </xsl:template>

  16: </xsl:stylesheet>

5. Now we need to modify the XSL between the <ns0:Root> tags to concatenate the field values from the source message.  This is the point where I start to construct the XSL syntax. To append the field values the following XSL was added between the root elements.

   1: <?xml version="1.0" encoding="utf-16"?>

   2: <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"

   3:                 xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s0" version="1.0"

   4:                 xmlns:ns0="http://FFSchema.Schema1"

   5:                 xmlns:s0="http://ECL.Online.BT.JobBooking.SITAAirNZBDS_FF">

   6:   <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />

   7:   <xsl:template match="/">

   8:     <xsl:apply-templates select="/s0:SitaBDS" />

   9:   </xsl:template>

  10:   <xsl:template match="/s0:SitaBDS">

  11:     <ns0:Root>

  12:

  13:       <!-- variable to hold appended values-->

  14:       <xsl:variable name="AppendedValue">

  15:         <!-- iterate through each line-->

  16:         <xsl:for-each select="BagageClaim/DS/Lines/Field">

  17:           <xsl:value-of select="./text()" />

  18:           <!-- add space -->

  19:           <xsl:value-of select="' '" />

  20:         </xsl:for-each>

  21:       </xsl:variable>

  22:

  23:       <!-- output the concatenated value -->

  24:       <xsl:element name="Field">

  25:         <xsl:value-of select="$AppendedValue"/>

  26:       </xsl:element>

  27:     </ns0:Root>

  28:   </xsl:template>

  29: </xsl:stylesheet>

6. You should be able to test the XSL syntax by setting the XML Document properties to a sample XML file. Right click anywhere on the XSLT file and select Properties on the context menu. Set the Input property to a valid sample XML source file. Once you have done that press the keys  (Ctrl + Alt + F5) to start the transformation without  debugging.

This should produce the following output.

<ns0:Root xmlns:ns0=”http://FFSchema.Schema1″><Field>This is the first line. This is the second line. This is the third line. </Field></ns0:Root>

7. Once we have tested the XSL code, we can now add that to our map using the Inline XSLT Functiod.

Remember when using XSL to populate a element, you are responsible for creating the destination node and all child nodes in your XSL. The final map looks like below:

Now testing the map with a real sample source message produces the following output.

Enjoy.