Question Re: Groovy script for XML to JSON conversion in Technology Q&A

Groovy script for XML to JSON conversion

anirban_pwc — Wed, 08 Apr 2020 21:00:24 GMT

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

Re: Groovy script for XML to JSON conversion

ibibhu — Wed, 08 Apr 2020 21:52:16 GMT

HI,

Please check your regular expression and change it as given below :-

String output = body.replaceAll("\"(false)\"", "\$1");

"( )" => Groups multiple tokens together and creates a capture group for extracting a sub string.

I tried the same but i didn't get any error.

Input : -

Output:

As already shared by 7a519509aed84a2c9e6f627841825b5a, that's the ideal way to typecast a given string to respective type.

Regards,
Bibhu

Re: Groovy script for XML to JSON conversion

MortenWittrock — Wed, 08 Apr 2020 21:59:40 GMT

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

Re: Groovy script for XML to JSON conversion

anirban_pwc — Wed, 08 Apr 2020 22:05:58 GMT

Hi Bibhu,

I received the same error as before:

Here is the updated code:

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;
}

Thank you.

Re: Groovy script for XML to JSON conversion

anirban_pwc — Wed, 08 Apr 2020 22:08:26 GMT

Hi Morten,

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

Regards,

Anirban

Re: Groovy script for XML to JSON conversion

MortenWittrock — Wed, 08 Apr 2020 22:42:53 GMT

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

Re: Groovy script for XML to JSON conversion

anirban_pwc — Thu, 09 Apr 2020 00:32:48 GMT

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

Re: Groovy script for XML to JSON conversion

MortenWittrock — Thu, 09 Apr 2020 09:57:44 GMT

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

Re: Groovy script for XML to JSON conversion

anirban_pwc — Thu, 09 Apr 2020 13:06:10 GMT

Thank you, Bibhu - your answer is a valid one and it worked for me after I removed the "Ccript Function" as suggested by Morten, but unfortunately I cannot accept 2 answers.

Thanks again for your time and support.

Regards,

Anirban

Re: Groovy script for XML to JSON conversion

anirban_pwc — Thu, 09 Apr 2020 13:08:30 GMT

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

Re: Groovy script for XML to JSON conversion

MortenWittrock — Thu, 09 Apr 2020 14:06:03 GMT

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