January 12, 2010
Posted by admin
transforming a flattened file to hierarchical canonical schema
I ran into a challenge having to map a flattened file data, from SAP’s RFC call, to a hierarchically structured canonical schema.
Source:
<YITAB> <RecType /> <CDATA /> </YITAB>
Canonical:
<Header> <Detail> <DetailSpecialInstruction /> </Detail> <HeaderSpecialInstruction /> </Header>
rules:
1. if RecType = 001 then header record; if RecType = 002 then Detail Record
2. if RecType = 003 and substring(CDATA,1,5) <> “00000″, then it is a DetailSpecialInstruction record and substring(CDATA,1,5) = the related detail record line number
3. if RecType = 003 and substring(CDATA,1,5) = “00000″, then it is a HeaderSpecialInstruction record
As you can see, this is a complicated transformation exercise. Not only does the map needs to convert the flattened dataset to a hierarchical structure with related detail record and special instruction record grouped under the corresponding parent node, but also the key that ties the special instructions (both detail and header) are embedded inside the CDATA string.
I can’t make it this transformation happen visually inside the map, so I had to create a custom XSLT to implement these rules.
After yet another hair-jerking session and a crash-course on XSLT tags, I got it to work! The trick is to use template with parameters to recursively loop through the entire data and build the child elements of each node. This may not be the cleanest way to do it, but I got it to work and I’m done with it.
Here is the XSLT code:
<?xml version=”1.0″ encoding=”utf-8″?>
<xsl:stylesheet version=”1.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns:ns0=”http://Rev.Eai.DeliveryNote.Schemas.DeliveryNoteSAPGrouped”
xmlns:ns1=”http://Microsoft.LobServices.Sap/2007/03/Rfc/”
xmlns:ns2=”http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/”
>
<!– Defining the key used for locating PickTicketNum –>
<xsl:key name=”rows-by-CPKTNO” match=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB” use=”ns2:CPKTNO” />
<xsl:template match=”/”>
<ns0:Groups>
<!– Looping the unique categories to get a group for –>
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB[count(. | key('rows-by-CPKTNO', ns2:CPKTNO)[1]) = 1]”>
<!– Creating a new group and set the numberOfRows –>
<xsl:call-template name=”group”>
<xsl:with-param name=”parentCPKTNO” select=”ns2:CPKTNO” />
</xsl:call-template>
</xsl:for-each>
</ns0:Groups>
</xsl:template>
<xsl:template name=”group”>
<xsl:param name=”parentCPKTNO” />
<DeliveryNote CPKTNO=”{ns2:CPKTNO}”>
<!– Loop all the rows within each PickTicketNum –>
<xsl:for-each select=”key(‘rows-by-CPKTNO’, ns2:CPKTNO)”>
<xsl:if test=”current()/ns2:CRECTYPE = ’001′”>
<!– Create header record –>
<HeaderRecord>
<xsl:call-template name=”body”>
</xsl:call-template>
</HeaderRecord>
</xsl:if>
</xsl:for-each>
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB”>
<xsl:if test=”current()/ns2:CRECTYPE = ’002′ and current()/ns2:CPKTNO = $parentCPKTNO”>
<DetailRecord>
<xsl:call-template name=”body”>
</xsl:call-template>
<xsl:call-template name=”DetailspecialInstruction”>
<xsl:with-param name=”parentDetailCPKTNO” select=”$parentCPKTNO” />
<xsl:with-param name=”parentDetailLineNbr” select=”substring(current()/ns2:CDATA,1,5)” />
</xsl:call-template>
</DetailRecord>
</xsl:if>
</xsl:for-each>
<xsl:call-template name=”HeaderspecialInstruction”>
<xsl:with-param name=”parentCPKTNO” select=”$parentCPKTNO” />
</xsl:call-template>
</DeliveryNote>
</xsl:template>
<xsl:template name=”DetailspecialInstruction”>
<xsl:param name=”parentDetailCPKTNO” />
<xsl:param name=”parentDetailLineNbr” />
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB”>
<xsl:if test=”current()/ns2:CRECTYPE = ’003′ and current()/ns2:CPKTNO = $parentDetailCPKTNO and substring(current()/ns2:CDATA, 1,5) = $parentDetailLineNbr”>
<DetailspecialInstruction>
<xsl:call-template name=”body”>
</xsl:call-template>
</DetailspecialInstruction>
</xsl:if>
</xsl:for-each>
</xsl:template>
<xsl:template name=”HeaderspecialInstruction”>
<xsl:param name=”parentCPKTNO” />
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB”>
<xsl:if test=”current()/ns2:CRECTYPE = ’003′ and current()/ns2:CPKTNO = $parentCPKTNO and substring(current()/ns2:CDATA, 1,5) = ’00000′”>
<HeaderspecialInstruction>
<xsl:call-template name=”body”>
</xsl:call-template>
</HeaderspecialInstruction>
</xsl:if>
</xsl:for-each>
</xsl:template>
<xsl:template name=”body”>
<CFROMSID>
<xsl:value-of select=”ns2:CFROMSID”/>
</CFROMSID>
<CFROMTABLE>
<xsl:value-of select=”ns2:CFROMTABLE”/>
</CFROMTABLE>
<CTOSID>
<xsl:value-of select=”ns2:CTOSID”/>
</CTOSID>
<CTOTABLE>
<xsl:value-of select=”ns2:CTOTABLE”/>
</CTOTABLE>
<CKEY>
<xsl:value-of select=”ns2:CKEY”/>
</CKEY>
<CDTCREATE>
<xsl:value-of select=”ns2:CDTCREATE”/>
</CDTCREATE>
<CDATALEN>
<xsl:value-of select=”ns2:CDATALEN”/>
</CDATALEN>
<CDATA_OFFSET>
<xsl:value-of select=”ns2:CDATA_OFFSET”/>
</CDATA_OFFSET>
<CRECTYPE>
<xsl:value-of select=”ns2:CRECTYPE”/>
</CRECTYPE>
<CCOMPANY>
<xsl:value-of select=”ns2:CCOMPANY”/>
</CCOMPANY>
<CDIV>
<xsl:value-of select=”ns2:CDIV”/>
</CDIV>
<CPKTNO>
<xsl:value-of select=”ns2:CPKTNO”/>
</CPKTNO>
<CDATA>
<xsl:value-of select=”ns2:CDATA”/>
</CDATA>
<CDTPROCESS>
<xsl:value-of select=”ns2:CDTPROCESS”/>
</CDTPROCESS>
<CDTARCHIVE>
<xsl:value-of select=”ns2:CDTARCHIVE”/>
</CDTARCHIVE>
<STATUS>
<xsl:value-of select=”ns2:STATUS”/>
</STATUS>
<MSGNO>
<xsl:value-of select=”ns2:MSGNO”/>
</MSGNO>
</xsl:template>
</xsl:stylesheet>
