Convert complex XML structure to CSV in SAP CPI

[Distinct_Sky]

Convert complex XML to CSV in SAP CPI

In integrations, we frequently come across scenarios where an XML file needs to be converted to CSV (or vice versa). To handle this, SAP CPI provides out of the box functionality to handle conversion of simple XML structures to CSV. However, it lacks the capability to convert complex structures.

In this article, I will showcase one of the ways you can convert complex XML structure to CSV.

Let’s take the example of the below XML. It has a single Header, multiple Item and single Summary.

The iflow is configured in the pattern below.

Content Modifier (CM_InputXML): For testing purposes, I am using content modifier to initialize the XML. You can skip this step if you are already getting XML from any other source (FTP, SFTP, HTTP, Request-Reply etc).

XSLT (XS_Header, XS_Item and XS_Summary): Used to extract the relevant nodes from the XML.

XML to CSV converter (XC_Header, XC_Item and XC_Summary): Used to convert simple XML structure to CSV.

Gather (GA_AllThree): Used to combine the 3 structures. 

To extract the Header node, use below XSLT:

Spoiler

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

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="Root">
<Root>
        <xsl:copy-of select="Header"/> 
</Root>

</xsl:template> </xsl:stylesheet>

 Similarly, use above XSLT to extract Item and Summary nodes. Just replace Header with Item and Summary in the XSLT.

Once executed, the above XSLTs will provide you with 3 simple XML structures. One each for Header, Item and Summary.

Instead of the XSLTs, you can use standard Message mapping as well. However, that requires creation of XSDs and regular updates if the file layout changes. I prefer XSLT since it’s more robust and requires less changes.

Now all we need to do is use the standard function to convert the simple XML structures to CSV. Use XML to CSV convert to achieve this.

Gather all 3 and you are good to go.

Deploy the iflow and you will see following results: