XML To Json Converter | Set Json element Data Type |Replication PI/PO XML TO Json Conversion in CPI

[Chandan_]

Hello everyone 👋,

I recently reviewed an  XML TO JSON CONVERTER BLOG  and noticed that after conversion, all data is returned as strings (enclosed in double quotes).

In this blog, we will explore how to assign appropriate data types to respective JSON elements using a Groovy script. 🛠️
and how we can replicate SAP PI/PO Xml to Json conversion  in sap cpi.

So, I won’t take much of your time. Let’s get straight to the point. Below is the XML structure that serves as our source payload:

<?xml version="1.0" encoding="UTF-8"?>
<college>
    <collegeDetails>
        <name>ABC University</name>
        <location>New York, USA</location>
        <established>1965</established>
        <contactEmail>info@abcuniversity.edu</contactEmail>
    </collegeDetails>
    <eligibility>
        <criteria>
            <attendance>75%</attendance>
            <minimumGPA>2.5</minimumGPA>
            <disciplinaryActions>true</disciplinaryActions>
        </criteria>
    </eligibility>
    <students>
        <student id="1">
            <name>John Doe</name>
            <age>20</age>
            <gender>Male</gender>
            <department>Computer Science</department>
            <email>john.doe@example.com</email>
            <attendance>80%</attendance>
            <GPA>3.2</GPA>
            <eligibleForExam>true</eligibleForExam>
        </student>
        <student id="2">
            <name>Jane Smith</name>
            <age>22</age>
            <gender>Female</gender>
            <department>Mathematics</department>
            <email>jane.smith@example.com</email>
            <attendance>78%</attendance>
            <GPA>3.5</GPA>
            <eligibleForExam>true</eligibleForExam>
        </student>
        <student id="3">
            <name>Michael Brown</name>
            <age>21</age>
            <gender>Male</gender>
            <department>Physics</department>
            <email>michael.brown@example.com</email>
            <attendance>70%</attendance>
            <GPA>2.8</GPA>
            <eligibleForExam>false</eligibleForExam>
        </student>
        <student id="4">
            <name>Emily Johnson</name>
            <age>23</age>
            <gender>Female</gender>
            <department>Chemistry</department>
            <email>emily.johnson@example.com</email>
            <attendance>90%</attendance>
            <GPA>3.9</GPA>
            <eligibleForExam>true</eligibleForExam>
        </student>
    </students>
<students>
        <student id="11">
            <name>John Doe</name>
            <age>20</age>
            <gender>Male</gender>
            <department>Computer Science</department>
            <email>john.doe@example.com</email>
            <attendance>60%</attendance>
            <GPA>2.2</GPA>
            <eligibleForExam>false</eligibleForExam>
        </student>
</students>
</college>

We need to convert it into JSON format where the data type of elements should be correctly set.

XML to JSON CONVERSION RULE :

Element	Array type	Data Type
established		Integer
minimumGPA		Decimal
students	True
student	True
disciplinaryActions		Boolean
student id		Integer
age		Integer
GPA		Decimal
eligibleForExam		Boolean
Remaining all elements		String

In SAP CPI, the standard Pallet function considers everything as a string. So, in this case, we will use the streaming option and a Groovy script to handle the conversion.

STEP1. the basic flow structure.

STEP2: Add payload in content modifier

Now we will execute and see the output.

{
    "college": {
        "collegeDetails": {
            "name": "ABC University",
            "location": "New York, USA",
            "established": "1965",
            "contactEmail": "info@abcuniversity.edu"
        },
        "eligibility": {
            "criteria": {
                "attendance": "75%",
                "minimumGPA": "2.5",
                "disciplinaryActions": "true"
            }
        },
        "students": {
            "student": {
                "@id": "1",
                "name": "John Doe",
                "age": "20",
                "gender": "Male",
                "department": "Computer Science",
                "email": "john.doe@example.com",
                "attendance": "80%",
                "GPA": "3.2",
                "eligibleForExam": "true"
            },
            "student": {
                "@id": "2",
                "name": "Jane Smith",
                "age": "22",
                "gender": "Female",
                "department": "Mathematics",
                "email": "jane.smith@example.com",
                "attendance": "78%",
                "GPA": "3.5",
                "eligibleForExam": "true"
            },
            "student": {
                "@id": "3",
                "name": "Michael Brown",
                "age": "21",
                "gender": "Male",
                "department": "Physics",
                "email": "michael.brown@example.com",
                "attendance": "70%",
                "GPA": "2.8",
                "eligibleForExam": "false"
            },
            "student": {
                "@id": "4",
                "name": "Emily Johnson",
                "age": "23",
                "gender": "Female",
                "department": "Chemistry",
                "email": "emily.johnson@example.com",
                "attendance": "90%",
                "GPA": "3.9",
                "eligibleForExam": "true"
            }
        },
        "students": {
            "student": {
                "@id": "11",
                "name": "John Doe",
                "age": "20",
                "gender": "Male",
                "department": "Computer Science",
                "email": "john.doe@example.com",
                "attendance": "60%",
                "GPA": "2.2",
                "eligibleForExam": "false"
            }
        }
    }
}

Here we can observe that we are not getting the correct data type of elements and Array elements.
First we will fix array element. 
STEP 3 . set array element in Xml to Json converter
To set the JSON array element, we need to choose "Specified Ones" from the dropdown for "Convert XML Element to JSON Array" and then provide the absolute XPath for the array element.
and then we have to give the absolute Xpath for array element

Output of xml to json Converter.

{
    "college": {
        "collegeDetails": {
            "name": "ABC University",
            "location": "New York, USA",
            "established": "1965",
            "contactEmail": "info@abcuniversity.edu"
        },
        "eligibility": {
            "criteria": {
                "attendance": "75%",
                "minimumGPA": "2.5",
                "disciplinaryActions": "true"
            }
        },
        "students": [
            {
                "student": [
                    {
                        "@id": "1",
                        "name": "John Doe",
                        "age": "20",
                        "gender": "Male",
                        "department": "Computer Science",
                        "email": "john.doe@example.com",
                        "attendance": "80%",
                        "GPA": "3.2",
                        "eligibleForExam": "true"
                    },
                    {
                        "@id": "2",
                        "name": "Jane Smith",
                        "age": "22",
                        "gender": "Female",
                        "department": "Mathematics",
                        "email": "jane.smith@example.com",
                        "attendance": "78%",
                        "GPA": "3.5",
                        "eligibleForExam": "true"
                    },
                    {
                        "@id": "3",
                        "name": "Michael Brown",
                        "age": "21",
                        "gender": "Male",
                        "department": "Physics",
                        "email": "michael.brown@example.com",
                        "attendance": "70%",
                        "GPA": "2.8",
                        "eligibleForExam": "false"
                    },
                    {
                        "@id": "4",
                        "name": "Emily Johnson",
                        "age": "23",
                        "gender": "Female",
                        "department": "Chemistry",
                        "email": "emily.johnson@example.com",
                        "attendance": "90%",
                        "GPA": "3.9",
                        "eligibleForExam": "true"
                    }
                ]
            },
            {
                "student": [
                    {
                        "@id": "11",
                        "name": "John Doe",
                        "age": "20",
                        "gender": "Male",
                        "department": "Computer Science",
                        "email": "john.doe@example.com",
                        "attendance": "60%",
                        "GPA": "2.2",
                        "eligibleForExam": "false"
                    }
                ]
            }
        ]
    }
}

Now we will fix the Json element Data Types.
STEP4. Set JSON element Data Type.
Here we will use Groovy script to set the data types for JSON element.
👉 note:  We need not to  do anything for String Type.

import groovy.json.JsonSlurper
import groovy.json.JsonBuilder
import com.sap.gateway.ip.core.customdev.util.Message

def Message processData(Message message) {
    def body = message.getBody(java.lang.String)
    def jsonSlurper = new JsonSlurper()
    def json = jsonSlurper.parseText(body)

    // Call the conversion function with the root of the JSON
    convertGPaths(json)

    // Convert the modified JSON back to a string
    def modifiedJsonStr = new JsonBuilder(json).toPrettyString()

    message.setBody(modifiedJsonStr)
    return message
}

def convertGPaths(node) {
    if (node instanceof Map) {
        node.each { key, value ->
            if (key == "established" || key == "student id" || key == "age") {
                node[key] = value as Integer
            } else if (key == "disciplinaryActions" || key == "eligibleForExam") {
                if (value.toString().equalsIgnoreCase("true")) {
                    node[key] = true
                } else if (value.toString().equalsIgnoreCase("false")) {
                    node[key] = false
                } else {
                    node[key] = value
                }
            } else if (key == "minimumGPA" || key == "GPA") {
                node[key] = value as Double
            } else if (value instanceof Map || value instanceof List) {
                convertGPaths(value)
            } else {
                node[key] = value
            }
        }
    } else if (node instanceof List) {
        node.each { item ->
            if (item instanceof Map || item instanceof List) {
                convertGPaths(item)
            }
        }
    }
}

Now, combining all pieces together, our iflow looks like below:

And the final output after deploying the iflow.

{
  "college": {
    "collegeDetails": {
      "name": "ABC University",
      "location": "New York, USA",
      "established": 1965,
      "contactEmail": "info@abcuniversity.edu"
    },
    "eligibility": {
      "criteria": {
        "attendance": "75%",
        "minimumGPA": 2.5,
        "disciplinaryActions": true
      }
    },
    "students": [
      {
        "student": [
          {
            "@id": "1",
            "name": "John Doe",
            "age": 20,
            "gender": "Male",
            "department": "Computer Science",
            "email": "john.doe@example.com",
            "attendance": "80%",
            "GPA": 3.2,
            "eligibleForExam": true
          },
          {
            "@id": "2",
            "name": "Jane Smith",
            "age": 22,
            "gender": "Female",
            "department": "Mathematics",
            "email": "jane.smith@example.com",
            "attendance": "78%",
            "GPA": 3.5,
            "eligibleForExam": true
          },
          {
            "@id": "3",
            "name": "Michael Brown",
            "age": 21,
            "gender": "Male",
            "department": "Physics",
            "email": "michael.brown@example.com",
            "attendance": "70%",
            "GPA": 2.8,
            "eligibleForExam": false
          },
          {
            "@id": "4",
            "name": "Emily Johnson",
            "age": 23,
            "gender": "Female",
            "department": "Chemistry",
            "email": "emily.johnson@example.com",
            "attendance": "90%",
            "GPA": 3.9,
            "eligibleForExam": true
          }
        ]
      },
      {
        "student": [
          {
            "@id": "11",
            "name": "John Doe",
            "age": 20,
            "gender": "Male",
            "department": "Computer Science",
            "email": "john.doe@example.com",
            "attendance": "60%",
            "GPA": 2.2,
            "eligibleForExam": false
          }
        ]
      }
    ]
  }
}

Conclusion:

In this blog, we have demonstrated how to achieve PO XML to JSON conversion rules in SAP CPI and set the JSON array elements and data types. This approach can be particularly useful when migrating interfaces from PI/PO to SAP IS, especially when dealing with complex XML to JSON conversion rules.

Feel free to optimize this solution further as needed, and I welcome any feedback or improvements.