The Integration blog covers the latest in product updates, best practices, customer stories, and more.
-
Integration
September 17, 2020
How to create a XSLT map that reads many correlated payloads
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
-
Very good information shared