Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Chandan_
Explorer

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 :

ElementArray typeData Type
established Integer
minimumGPA Decimal
studentsTrue 
studentTrue 
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.

Screenshot 2024-05-24 162322.png

STEP2: Add payload in content modifier


Screenshot 2024-05-24 163950.png

 

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

Screenshot 2024-05-24 165729.png

 

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:

Screenshot 2024-05-24 181446.png

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.

 

Labels in this area