In this article, I describe how to segregate and regroup source XML data using XSLT mapping. Let us understand this using a small example. Lets say we have a flat XML structure consisting of Purchase Order (PO) Items of a couple of Purchase Orders (POs). And our intention is to transform this XML into another XML such that the PO Items are grouped according to their PO numbers and header level and item level data is segregated. Header level data should occur only once and item level data should occur as many times as many number of items exist in a particular PO.
Source XML Structure
Desired Target Structure
XSLT Mapping Program
Now let us understand the XSLT Mapping Program. You can download the source code of XSLT mapping program here: POExtract_to_POData.xsl.
<xsl:stylesheet> tag indicates that this is an XSL document
<xsl:template> tag is used to match “/” i.e. entire source document. So the rules within <xsl:template> and </xsl:template> will be used produce the target XML.
We store the current PO Number in a variable called currentPONumber using <xsl:variable> tag. <xsl:for-each> checks whether the current and previous PO numbers differ. Only if the numbers differ, the elements will be produced at the output. Thus, the Header data will be produced at the output only once for each PO number.
We have another <xsl:for-each> loop within the existing <xsl:for-each>. This is used to produce item level fields at the output. The <xsl:for-each> tag compares the PO number with the PO number stored in the variable currentPONumber. Only if the values match, the underlying elements will be produced at the output. Thus, item level details will be produced under the current PO row node only if they belong to the current PO.
<xsl:value-of> tags are used to move individual XML field values to the target structure.
Testing the XSLT Mapping
You can test the mapping by compressing the XSL file into a ZIP archive and importing it into IR/ESR. You need to create appropriate IR objects and use the XSLT Program in your interface mapping. I have explained detailed steps of doing this in the article – Beginner’s Guide to XSLT Mapping in SAP PI.
There is another simple way of verifying the functionality of our XSLT program. Open the source XML file and add the following line immediately after the first line.
<?xml-stylesheet type="text/xsl" href="POExtract_to_POData.xsl"?>
Make sure the name of XSL file is correct. Save the file and open it in a browser. If everything goes well you should see the correctly transformed values in the browser. However, you will not be able to see the XML tags in the browser output. To see the XML tags you will need to test the program using one of the XSLT development tools like Altova XMLSpy, Altova MapForce, Stylus Studio etc or you can directly test the program in IR/ESR as explained earlier.
You can download the source XML file and XSLT Mapping program for testing. To download, right-click the download links and choose ‘Save target as…’ or ‘Save link as…’ option.
