X

The Integration blog covers the latest in product updates, best practices, customer stories, and more.

How to create a XSLT map that reads many correlated payloads

Jorge Herreria
Software Developer 5.

Summary

On this post will see a way for creating XSLT maps that need to loop thought different sources (aka input payloads) which their instances are correlated by key fields.

Example for 1:0..n and 1:1 relationships between sources

I will use the Business units and Employees classic example: Each Business Unit can have  0..n Employees (1:0..n relationship). Also the G/L Accounts source with a 1:1 correlation with Business Units. 

I want to create a XSLT Map that puts them together.

Here are the sources (aka input payloads)

$BusinessUnits

 <compay>
  <bu> 
    <id>SD</id> <name>Software Development</name> <accounbtid>i9</accountid> 
  </bu> 
  <bu> 
    <id>BS</id> <name>Sales</name> <accounbtid>i1</accountid>                
  </bu>
  <bu> 
    <id>MD</id> <name>Marketing</name>  <accounbtid>i2</accountid>          
  </bu>
 </company>

 

$Employees

 <people>
  <emp> <buid>SD</budi> <name>Jorge Herreria</name> </emp> 
  <emp> <buid>SD</buid> <name>Steve Jobs</name> </emp>
  <emp> <buid>BS</buid> <name>Larry the Cable Guy</name> </emp> 
 </people>

$GLAccounts 
 <gl>     
   <account> <id>i1</id> <number>001.345</number> </account> 
   <account> <id>i2</id> <number>001.477</number> </account>
   <account> <id>i9</id> <number>001.223</number> </account>
</gl> 

As you may have derived, the link between the business unit and employees is the Business Unit ID. The "header" is the $BusinessUnit and the "detail" is the $Employees. The like for GL Accounts and Business units is the account ID.

 

Here's output needed:

<xxx>
  <yyy> 
    <BU id='SD'>Software Development</BU> 
    <empName>Jorge Herreria</empName> 
    <accNumber>001.223</accNumber>
  </yyy> 
  <yyy> 
    <BU id='SD'>Software Development</BU> 
    <empName>Steve Jobs</empName>    
    <accNumber>001.223</accNumber>
  </yyy>
  <yyy> 
    <BU id='BS'>Sales</BU>         
    <empName>Larry the Cable Guy</empName> 
    <accNumber>001.345</accNumber>
  </yyy> 
</xxx>

a Solution:

When the instances (records) of the sources have 1:1 correlation, using a predicate will perform well.

When the instances have 1:0..n correlation, using a xsl:for-each-group performs better than using predicates because avoids over-parsing the source.

Here's the XSLT:

<?xml version = '1.0' encoding = 'UTF-8'?>
<xsl:stylesheet version="2.0"  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
        xmlns:fn="http://www.w3.org/2005/xpath-functions"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:param name="BusinessUnits" />
  <xsl:param name="Employees" />
  <xsl:param name="GLAccounts"/>
  <xsl:template match="/" >
     <xxx>
        <xsl:for-each-group select="$Employees/people/employee" group-by="buid">
           <!-- this section will be executed only once per 'buid' -->
           <!-- Store the Business Unit Record in a variable -->
           <xsl:variable name="BURecord">
               <xsl:copy-of select="$BusinessUnits/company/bu[id = fn:current-grouping-key()]"/>
           </xsl:variable>
           <!-- Store the GL Account Record in a variable -->
           <xsl:variable name="GLAccountRecord">
               <xsl:copy-of select="$GLAccounts/gl/account[id = $BURecord/bu/accountid]" />
           </xsl:variable>
           <!-- end: executed only once per 'buid' -->
           <xsl:for-each select="current-group()">
               <!-- iterates the employees within the current 'buid' -->
               <yyy>
                   <BU id="{./buid}">
                       <xsl:value-of select="$BURecord/bu/name" />
                   </BU>
                   <empName>
                       <xsl:value-of select="./name" />
                   </empName>
                   <accNumber>
                        <xsl:value-of select="$GLAccountRecord/account/number"/>
                   </accNumber>
               </yyy>
            </xsl:for-each>
        </xsl:for-each-group>
     </xxx>
  </xsl:template>
</xsl:stylesheet>

Take away

When there is a 1:1 relationship, using predicates instead of <xsl:for-each-group> is faster because the xslt engine does not need to sort the data to create the group

When there is a 1:0..n relationship, using <xsl:for-each-group> will perform faster than using predicates, because using predicates will, in above example, parse the entire Business Unit source and GL Account source per every Employee. 

 

Some links:

XPath Predicates: https://www.w3schools.com/xml/xpath_syntax.asp

xsl:for-each example:  https://www.xml.com/pub/a/2003/11/05/tr.html

Join the discussion

Comments ( 1 )
  • tiku arya Monday, September 21, 2020
    Very good information shared
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.