Data transformation with IBM Integration Bus XSLT node

The XSLT node based on W3C standards for XSLT transformation language. The XSLT 1.0 and 2.0 are supported. XSLT turns out to be very different from the typical programming languages in use today. As some parts of ESQL (which is based on classical SQL) it is a declarative language as opposed to purely imperative Java PHP or C#. But in ESQL the variables can change their values and it can evolve the state of variables during the execution of the program. In another hand the XSLT is a functional programming language. That is to say that XSLT templates are not supposed to handle state during execution and there is no mutable variables. XSLT treat functions as a first-class data types and often uses recursion.

XSLT can only transform XML document. That said, it is important to underline that even if the input data must be structured in XML way in order to be processed by XSL template, the output of the XSLT transformation can be not only XML but other type of structured text data, such as CSV, TDS etc.

Being functional language XSLT transformations protected from errors caused by side effects and threading issues. There is solid scientific ground and variety of sources for education such as books tutorials and forums. XSLT 1.0 norm exists since 1999 (for fifteen years as the time of this writing) and the return on experience of usage is abundant, that lower risks related to adoption of the other often brand new technologies.

In XSLT language functions are expressed as template rules. Program flow apply the templates on incoming document in turn or launch recursion. The result of the transformation is sent to the output. The precise template to apply can be defined in the LocalEnvironment at runtime, or hard coded in the node properties. It is portable and supported by variety of the data transformation tools from different vendors big or small inside and outside of IBM.

Developers use Xpath in in order to navigate in the incoming XML document. In addition they can use Xpath functions library and operators.

Among limitations XSLT node are inability to handle map transport headers and environment trees. Impossibility to do manipulations or look ups in the databases. Programmer can not call external functions. No I/O support within the node. Despite human readability of the code, the language itself is nor easily intelligible for inexperienced programmer and is not the most readable when compared to number of commercially used languages.

As a sample let’s rewrite the existing transformation from esql to xslt

We have to create the TDS message from SAP IDOC

Input IDoc message (partially)

<?xml version="1.0" encoding="UTF-8"?>
<Q1:sap_zwmsstock2_01 xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_zwmsstock2_01" version="3.0.0" verb="Create" locale="en_US" delta="false">
 <Q1:Control_record>
 <Q5:sap_idoccontrol xmlns:Q5="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_idoccontrol" version="1.0.0" verb="" locale="en_US" delta="false">
 <Q5:Name_of_table_structure>EDI_DC40</Q5:Name_of_table_structure>
 <Q5:Client>130</Q5:Client>
 <Q5:IDoc_number>0000000000727158</Q5:IDoc_number>
 <Q5:SAP_Release_for_IDoc>45B</Q5:SAP_Release_for_IDoc>
 <Q5:Status_of_IDoc>30</Q5:Status_of_IDoc>
 <Q5:Direction_for_IDoc_transmission>1</Q5:Direction_for_IDoc_transmission>
 <Q5:Output_mode>2</Q5:Output_mode>
 <Q5:Name_of_basic_type>ZWMSSTOCK2_01</Q5:Name_of_basic_type>
 <Q5:Logical_message_type>ZWMSSTOCK2</Q5:Logical_message_type>
 <Q5:Sender_port>SAPD25</Q5:Sender_port>
 <Q5:Partner_type_of_sender>LS</Q5:Partner_type_of_sender>
 <Q5:Partner_number_of_sender>D25_130</Q5:Partner_number_of_sender>
 <Q5:Receiver_port>A000000018</Q5:Receiver_port>
 <Q5:Partner_type_of_recipient>LS</Q5:Partner_type_of_recipient>
 <Q5:Partner_number_of_recipient>NEON_OUT</Q5:Partner_number_of_recipient>
 <Q5:IDoc_creation_date>20060103</Q5:IDoc_creation_date>
 <Q5:IDoc_creation_time>083441</Q5:IDoc_creation_time>
 <Q5:EDI_ALE_Serialization_field>20060103083441</Q5:EDI_ALE_Serialization_field>
 </Q5:sap_idoccontrol>
 </Q1:Control_record>
 <Q1:Data_record>
 <Q2:sap_zwmsstock2_01_cwdata xmlns:Q2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_zwmsstock2_01_cwdata" version="3.0.0" verb="" locale="en_US" delta="false">
 <Q2:sap_zwmsstock2_01_z2wmsstock2000 size="132">
 <Q4:sap_zwmsstock2_01_z2wmsstock2000 xmlns:Q4="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_zwmsstock2_01_z2wmsstock2000" version="3.0.0" verb="" locale="en_US" delta="false">
 <Q4:WMS_transactiecode>07</Q4:WMS_transactiecode>
 <Q4:Plant>6101</Q4:Plant>
 <Q4:Storage_location>6500</Q4:Storage_location>
 <Q4:Material_number>000000000000200001</Q4:Material_number>
 <Q4:Batch_number/>
....
 <Q2:sap_zwmsstock2_01_z2wmsstock2_end000>
 <Q3:sap_zwmsstock2_01_z2wmsstock2_end000 xmlns:Q3="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_zwmsstock2_01_z2wmsstock2_end000" version="3.0.0" verb="" locale="en_US" delta="false">
 <Q3:WMS_transactiecode>07</Q3:WMS_transactiecode>
 <Q3:Plant>6101</Q3:Plant>
 <Q3:Storage_location>6500</Q3:Storage_location>
 </Q3:sap_zwmsstock2_01_z2wmsstock2_end000>
 </Q2:sap_zwmsstock2_01_z2wmsstock2_end000>
 </Q2:sap_zwmsstock2_01_cwdata>
 </Q1:Data_record>
 <Q1:ObjectEventId>SAP25Connector_1136273683953_98</Q1:ObjectEventId>
</Q1:sap_zwmsstock2_01>

Output message to send to Warehouse Management Store (WMS) Sattstore ™ partially

00 SAP-WMS-00800 0000000000727158 2014-09-07 22:43:37

0700000000000020000161016500 250 0 0

0700000000000020000261016500 515 0 0

0700000000000020000361016500 8084 0 0

0700000000000010136361016500101363 0.001 0.01 0.1

07 61016500

99

The ESQL Code

 SET OutputRoot.Properties.MessageSet = 'SAP_WMS_00800';
 SET OutputRoot.Properties.MessageType = 'WMS_GETSTOCK_COMP';
 SET OutputRoot.Properties.MessageFormat = 'TDS';
 DECLARE INREF REFERENCE TO InputBody.ns1:Data_record.ns2:sap_zwmsstock2_01_cwdata.ns2:sap_zwmsstock2_01_z2wmsstock2000;
 DECLARE CREC REFERENCE TO "InputBody".ns1:Control_record.ns5:sap_idoccontrol;
 ---------------------------------------------------------------------------------------
 -- Map Start of file line.
 --------------------------------------------------------------------------------------- 
 SET OutputRoot.MRM.SOF.SOFTranscode = '00';
 SET OutputRoot.MRM.SOF.SOF2space = '  ';
 SET OutputRoot.MRM.SOF.SOFInterface = 'SAP-WMS-00800';
 SET OutputRoot.MRM.SOF.SOF2space = '  ';
 SET OutputRoot.MRM.SOF.SOFRefSender = CREC.ns5:IDoc_number;
 SET OutputRoot.MRM.SOF.SOF2space = '  ';
 SET OutputRoot.MRM.SOF.SOFDate = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'yyyy-MM-dd');
 SET OutputRoot.MRM.SOF.SOF2space = '  ';
 SET OutputRoot.MRM.SOF.SOFTime = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'HH:mm:ss'); 

  ---------------------------------------------------------------------------------------
 -- Map each z2wmsstock2000 segment.
 ---------------------------------------------------------------------------------------
 SET I = 1;
 SET C = INREF.size;
 WHILE I <= C DO
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Transcode = INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:WMS_transactiecode;
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Matnr = INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Material_number;
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Plant = INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Plant;
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Storage_location = INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Storage_location;
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Batch_number = INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Batch_number;
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Stock_unres = DecimalLengthCheck(INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Valuated_stock_with_unrestricted_use, 9);
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Stock_qual = DecimalLengthCheck(INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Stock_in_quality_inspection, 9);
 SET OutputRoot.MRM.WMS_GETSTOCK[I].Blocked_stock = DecimalLengthCheck(INREF.ns3:sap_zwmsstock2_01_z2wmsstock2000[I].ns3:Blocked_stock , 9);
 SET I = I + 1;
 END WHILE;
 ---------------------------------------------------------------------------------------
 -- Map full last line with only transcode plant and storage filled.
 ---------------------------------------------------------------------------------------
 DECLARE INREFEND REFERENCE TO InputBody.ns1:Data_record.ns2:sap_zwmsstock2_01_cwdata.ns2:sap_zwmsstock2_01_z2wmsstock2_end000;
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Transcode = INREFEND.ns4:sap_zwmsstock2_01_z2wmsstock2_end000.ns4:WMS_transactiecode;
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Matnr = '';
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Plant = INREFEND.ns4:sap_zwmsstock2_01_z2wmsstock2_end000.ns4:Plant;
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Storage_location = INREFEND.ns4:sap_zwmsstock2_01_z2wmsstock2_end000.ns4:Storage_location;
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Batch_number = '';
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Stock_unres = '';
 SET OutputRoot.MRM.WMS_GETSTOCK_END.Stock_qual = '';
SET OutputRoot.MRM.WMS_GETSTOCK_END.Blocked_stock = '';
 ---------------------------------------------------------------------------------------
 -- Map End Of File line
 ---------------------------------------------------------------------------------------
 SET OutputRoot.MRM.EOF.End_transcode = '99';

Let’s start from creation of the empty XSLT Template, lets use XSLT 1 as it’s easier to debug inside IBM Integration Toolkit

New->Other…->XML->XSL ( “Create a new XSL file” )

Map_IDoc_to_WMS.xsl

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
</xsl:stylesheet>

Then add code in order to only write footer record

—————————————————————————————

– Map End Of File line

—————————————————————————————

SET OutputRoot.MRM.EOF.End_transcode = ‘99′;

or

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  version="1.0">
 <xsl:template match="/">
 <xsl:call-template name="REC99">
 </xsl:call-template>
 </xsl:template>
 <xsl:template name="REC99">
 <xsl:value-of select="'99'" />
 </xsl:template>
</xsl:stylesheet>

Then test the code in the XSLT debugger. Unfortunately XSLT debugger is not integrated in the message flow debugger

Window->Open Perspective-> Other -> X (XML)

Right click on Map_IDoc_to_WMS.xsl

Debug As XSLT transformation.

Source XSL

C:\SVNL\ws3\SAP_WMS_00800_Flow_v1.4\Map_IDoc_to_WMS.xsl

Source XML

C:\SVNL\ws3\SAP_WMS_00800_Flow_v1.4\SAP_WMS_00800_01.xml

Processor : IBM Processor for XSLT 1.0

Press on Debug

Debug perspective opens.

Click continue, look to the XSL Transformation Output window.

<?xml version="1.0" encoding="UTF-8"?>99

Looks good

Remove XML document prefix, as we will generate the TDS file.

Add following line to the XSL file:

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="no" omit-xml-declaration="yes"/>

Rerun the test:

99

Great

Unfortunately with the XSLT 1.0 we should call java function in order to get date and time of transformation inside XSLT. Adding namespaces to acces Q5:IDoc_number value, creating header (00 record ), adding unix line feed after each record:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:java="http://xml.apache.org/xslt/java" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_zwmsstock2_01" xmlns:Q14="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/sap_idoccontrol">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="no" omit-xml-declaration="yes"/>
 <xsl:template match="/">
 <xsl:call-template name="REC00">
 </xsl:call-template>
 <xsl:call-template name="REC99">
 </xsl:call-template>
 </xsl:template>
 <xsl:template name="REC00">
 <xsl:for-each select="Q1:sap_zwmsstock2_01/Q1:Control_record">
 <xsl:value-of select="'00'" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="'SAP-WMS-00800'" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="Q14:sap_idoccontrol/Q14:IDoc_number" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="java:format(java:java.text.SimpleDateFormat.new('yyyy-MM-dd'), java:java.util.Date.new())" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="java:format(java:java.text.SimpleDateFormat.new('HH:mm:ss'), java:java.util.Date.new())" />
 <xsl:text>&#xa;</xsl:text>
 </xsl:for-each>
 </xsl:template>
 <xsl:template name="REC99">
 <xsl:value-of select="'99'" />
 <xsl:text>&#xa;</xsl:text>
 </xsl:template>
</xsl:stylesheet>

Rerun the test:

00  SAP-WMS-00800  0000000000727158  2014-09-08  00:26:27
99

Nice !

Add 07 ordinary records

<!– Map each z2wmsstock2000 segment. –>

<xsl:for-each

select=“Q1:sap_zwmsstock2_01/Q1:Data_record/Q2:sap_zwmsstock2_01_cwdata/Q2:sap_zwmsstock2_01_z2wmsstock2000/Q4:sap_zwmsstock2_01_z2wmsstock2000″>

<xsl:value-of select=“Q4:WMS_transactiecode” />

<xsl:value-of select=“Q4:Material_number” />

<xsl:value-of select=“Q4:Plant” />

<xsl:value-of select=“Q4:Storage_location” />

<xsl:value-of select=“Q4:Batch_number” />

<xsl:value-of select=“Q4:Valuated_stock_with_unrestricted_use” />

<xsl:value-of select=“Q4:Stock_in_quality_inspection” />

<xsl:value-of select=“Q4:Blocked_stock” />

<xsl:text>&#xa;</xsl:text>

</xsl:for-each>

and the last record:

 <!-- Map full last line with only transcode plant and storage filled. -->
 <xsl:value-of select="Q1:sap_zwmsstock2_01/Q1:Data_record/Q2:sap_zwmsstock2_01_cwdata/Q2:sap_zwmsstock2_01_z2wmsstock2000/Q4:sap_zwmsstock2_01_z2wmsstock2000/Q4:WMS_transactiecode" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="Q1:sap_zwmsstock2_01/Q1:Data_record/Q2:sap_zwmsstock2_01_cwdata/Q2:sap_zwmsstock2_01_z2wmsstock2000/Q4:sap_zwmsstock2_01_z2wmsstock2000/Q4:Plant" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="Q1:sap_zwmsstock2_01/Q1:Data_record/Q2:sap_zwmsstock2_01_cwdata/Q2:sap_zwmsstock2_01_z2wmsstock2000/Q4:sap_zwmsstock2_01_z2wmsstock2000/Q4:Storage_location" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="'  '" />
 <xsl:value-of select="'  '" />
 <xsl:text>&#xa;</xsl:text>

Rerun the test

00  SAP-WMS-00800  0000000000727158  2014-09-08  02:24:30
0700000000000020000161016500       250.000         0.000         0.000
0700000000000020000261016500       515.000         0.000         0.000
....
0700000000000010136361016500101363000.001000.010000.100
070000000000001017746101650010177411.01.00
0700000000000010181361016500101813123456789.000123456789.123123456789
07  6101  6500 
99

The record is in the required format !

We add the new XSL Transform node to the message flow canvas, replace ESQL compute node “Map IDoc to WMS”, then initialize it’s Stylesheet name property.

There is still a need to initialize the output queue property in the message, to replace ESQL code below :

—————————————————————————————

– Determine output destination based on Plant.

—————————————————————————————

IF INREFEND.ns4:sap_zwmsstock2_01_z2wmsstock2_end000.ns4:Plant = ‘6100′

THEN SET OutputLocalEnvironment.Destination.MQ.DestinationData.queueName = ‘SAP.WMB.WMS_Z_H_STOCK_IN’;

ELSEIF INREFEND.ns4:sap_zwmsstock2_01_z2wmsstock2_end000.ns4:Plant = ‘6101′

THEN SET OutputLocalEnvironment.Destination.MQ.DestinationData.queueName = ‘SAP.WMB.WMS_H_H_STOCK_IN’;

ELSEIF INREFEND.ns4:sap_zwmsstock2_01_z2wmsstock2_end000.ns4:Plant = ‘6102′

THEN SET OutputLocalEnvironment.Destination.MQ.DestinationData.queueName = ‘SAP.WMB.WMS_B_H_STOCK_IN’;

ELSE SET OutputLocalEnvironment.Destination.MQ.DestinationData.queueName = ‘WBIMB_SAP_WMS_00800_NOHIT’;

END IF;

. Unfortunately it can not be done inside the XSLT Transformation node. The easiest way to do it – the visual mapping.

Finally our flow becomes as follows :

If we have wsdl for input and output data structures we can greatly simplify the transformation writing by using the appropriate tooling that allows XML Data Map creation and generation XSL code visually.

XSL is a tool often used to turn an XML file into HTML for a webpage. One of the very popular historically speaking framework to do this was Apache Cocoon. From the other side for someone with experience with PHP and HTML, he will find that XSL is similar to embedding PHP in HTML. As you define the new messages structure, you can set the value of fields to be Xpath functions. These functions are the same Xpath functions as in the mapping node.

While XSL is uneasy to understand it has an advantage over the Mapping Node because it is at least possible to read the functions and it is slightly faster. It is dynamic and flexible; this allows it to easily be used for any size message. As for the ESQL module transformation, there is no setup required to write the message. However, as we said previously, from a development standpoint, it is difficult to maintain and write because it assumes knowledge of the input XML message. If you have no knowledge of the incoming message, it will be difficult to determine what the XSL is doing. XSL can be also very difficult and limited when dealing with non-XML (output) messages.

To summarize, the XSL Transform Node is useful for quick and simple transformations but requires knowledge of input xml and can be difficult to maintain

Leave a Reply

You must be logged in to post a comment.



 
       
iBudget    |   No More Money Falling Through Your Fingers        Home page
 
App store
Available on the iPhone
 
    Why Using Feedburner in Blogs Is So Important?