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. 

image

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.

image

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.

image

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.

image

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:

image

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

image

Enjoy.

Advertisements
This entry was posted in Biztalk and tagged , , . Bookmark the permalink.

One Response to Using XSLT to map from many-to-one

  1. Jay Sprenkle says:

    Since xsl output is a stream you don’t have to store the appended values in a variable. You can also just output them directly. Your inline xsl would simplify down to one element.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s