Groovy script for XML to JSON conversion

[amallick]

Hello Experts,

I need to use Groovy script to overcome the shortcomings of the standard XML to JSON converter on the Cloud Platform Integration.

Here is the output JSON format I need to get:

{
"PO": "test21",
"Items": [{
"Id": "000010",
"Product": "XXXXX",
"Quantity": 5,
"Discount_Percent": 0,
"Unit_Price": 0,
"Pharmaceutical": false,
}]
}

But this is what I received out of the converter:

{
"PO": "test21",
"Items": [{
"Id": "000010",
"Product": "XXXXX",
"Quantity": "5.000",
"Discount_Percent": "0.000",
"Unit_Price": "",
"Pharmaceutical": "false",
}]
}

I'm not a Groovy expert and tried utilizing the one provided in the SAP note to replace "false" with false using the following groovy but that didn't work:

import com.sap.gateway.ip.core.customdev.util.Message;

def Message processData(Message message) {
def body = message.getBody(java.lang.String) as String;
String output = body.replaceAll("\"false\"", "\$1");
message.setBody(output);
return message;
}

Can someone please help with a groovy script that will work for me?

Thanks for your time.

Anirban

[MortenWittrock]

Hi Anirban

How this conversion should work is not completely clear. Might Unit_Price contain a price, or is it always empty and should be replaced with zero? And can Quantity and Discount_Percent have decimals, that should be kept? Or do you want to throw away everything after the decimal separator?

Regards,

Morten

[amallick]

Hi Morten,

The Unit_Price will have a decimal value but the Discount_Percent and Quantity are integers. Sorry for the confusion.

Regards,

Anirban

[MortenWittrock · Accepted solution]

Hi Anirban

Here's a Groovy script that cleans up the JSON. It does so without using regular expressions, and it only updates the values, that need to be updated.

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

def Message processData(Message message) {

    def json = message.getBody(java.lang.String)
    def jsonSlurper = new JsonSlurper()
    def object = jsonSlurper.parseText(json)

    object.Items.each { item -> 
        // Convert Pharmaceutical to a boolean value
        item.Pharmaceutical = (item.Pharmaceutical == 'false' ? false : true)
        // Convert Quantity to an integer
        item.Quantity = item.Quantity.toDouble().intValue()
        // Convert Discount_Percent to an integer
        item.Discount_Percent = item.Discount_Percent.toDouble().intValue()
        // If Unit_Price is empty, replace with zero, otherwise convert to double
        if (item.Unit_Price == '') {
            item.Unit_Price = 0
        } else {
            item.Unit_Price = item.Unit_Price.toDouble()
        }
    }

    message.setBody(JsonOutput.toJson(object))
    return message

}

When you add the script to a Script step, do not enter anything into the Script Function field. That field has a specific purpose, which is not describing what the script does.

Given your input, it produces the following output:

Regards,

Morten

[amallick · Accepted solution]

Thanks a lot, Morten for the detailed script.

But pardon my ignorance as I'm absolutely green on groovy, I just copied your script and saved with a ".groovy" extension and added to the iFlow. But I'm still getting a generic error:

I'm sure missing a fundamental step in the process. Do I need to install any JAR file or something?

Additionally, if I may add a follow up question for the script - what if I want to clean another element at the root level, for example, if the "PO" is an integer, should I address it as

object.root.each { item ->
item.PO = item.Quantity.toDouble().intValue()
}

Thank you again for your time and support.

Regards,

Anirban

[MortenWittrock · Accepted solution]

Hi amallick

As for the error, please note this sentence in the error message: "replaces double quotes from Boolean data types". That sentence does not occur in my script, so I'll take a wild guess that you entered it into the Script Function field, under the assumption that that's where you document what the script does. And this is now when I urge you to re-read my answer, particularly this bit:

"When you add the script to a Script step, do not enter anything into the Script Function field. That field has a specific purpose, which is not describing what the script does."

As for your follow-up question about cleaning up another value, that's now your job to experiment with. As I often say in here, it's a very bad idea to copy-paste code you don't understand into your solution.

So your next step is to study the script, and experiment with modifying it. Take a look at the JsonSlurper class in particular, since it does most of the work.

Regards,

Morten

[amallick · Accepted solution]

Thank you, Morten for drawing my attention to the "Script Function" and after removing the description, the script worked for me and I do understand the code, but wondering if you can help me understanding how I can work with the root level elements, like the "PO" element for example.

Thanks a ton,

Anirban

[MortenWittrock · Accepted solution]

Hi amallick

Look at how the code works, and improvise from there. You can get at the PO key with object.PO.

And now you really need to experiment and learn by yourself. Take the hint, dude.

Regards,

Morten