Skip to Content
0

JSON to XML Converter in HCI

May 18, 2017 at 07:04 AM

1.2k

avatar image
Former Member

Hi,

I am getting JSON data from a REST service using request-Reply pattern in HCI. As REST adapter is not available in HCI like PI, hence I am fetching the JSON data using HTTP adapter get method.

Now, I am trying to convert this JSON data to XML using standard JSON to XML converter pattern available for iFlow design in Eclipse.

This is my incoming JSON format -

[
{
"ID": 3,
"code": "ffee0192",
"price": 300,
"departureDate": "2016-01-20T00:00:00",
"origin": "MUA",
"destination": "LAX",
"emptySeats": 0,
"plane": {
"type": "Boeing 777",
"totalSeats": 300
}
}
]

But, I am getting this error -

Cannot transform JSON document to XML: JSON document does not start with '{'.

JSON format is absolutely fine. Can anyone tell me how should I configure. I had added one groovy script as well to replace [ and ]. But, still no luck.

Any idea, whats going wrong. This is very easy to configure in PI.

Thanks,

Apu

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Sriprasad Shivaram Bhat May 19, 2017 at 08:26 AM
2

Hello Apu,

Agree on your point.As per SAP documentation JSON texts starting with lists are not supported in Convertor.

Looks like convertor is too much immature to handle your requirement.

Before you pass JSON message to convertor you have to make some changes to it by adding root and element nodes( Just string transformations,not required to import any jars)

Script before JSON to XML Convertor:

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import groovy.json.*

def Message processData(Message message) {
    //Body 
    def jsonOP = message.getBody(String.class);
    jsonOP=jsonOP.toString()
    
    def json_to_str=jsonOP.substring(1, jsonOP.length()- 1);
    json_to_str="{\"Root\": [{\"Element\":["+json_to_str+"]}]}"
    
    message.setBody(json_to_str);
    return message;
}

Input JSON:

[{
        "ID": 1,
        "code": "rree0001",
        "price": 541,
        "departureDate": "2016-01-20T00:00:00",
        "origin": "MUA",
        "destination": "LAX",
        "emptySeats": 0,
        "plane": {
            "type": "Boeing 787",
            "totalSeats": 200
        }
    }, {
        "ID": 2,
        "code": "eefd0123",
        "price": 300,
        "departureDate": "2016-01-25T00:00:00",
        "origin": "MUA",
        "destination": "CLE",
        "emptySeats": 7,
        "plane": {
            "type": "Boeing 747",
            "totalSeats": 345
        }
    }
]

Output XML:

<?xml version='1.0' encoding='UTF-8'?>
<Root>
    <Element>
        <ID>1</ID>
        <code>rree0001</code>
        <price>541</price>
        <departureDate>2016-01-20T00:00:00</departureDate>
        <origin>MUA</origin>
        <destination>LAX</destination>
        <emptySeats>0</emptySeats>
        <plane>
            <type>Boeing 787</type>
            <totalSeats>200</totalSeats>
        </plane>
    </Element>
    <Element>
        <ID>2</ID>
        <code>eefd0123</code>
        <price>300</price>
        <departureDate>2016-01-25T00:00:00</departureDate>
        <origin>MUA</origin>
        <destination>CLE</destination>
        <emptySeats>7</emptySeats>
        <plane>
            <type>Boeing 747</type>
            <totalSeats>345</totalSeats>
        </plane>
    </Element>
</Root>

or Else you can build Output XML in Script only by adding all the required logic to avoid convertor step [ Yet to give a try from my end ]

Regards,

Sriprasad Shivaram Bhat


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Sriprasad-

I have the following JSON output. I am trying to use your code it is not working and am sure am not understaning this behaviour very well.

Please let me know if you have any taught.

Regards

HS

{ "results": [ { "day": "2018-04-07", "new_device": "f", "users": 288 }, { "day": "2018-04-07", "new_device": "t", "users": 38 }, { "day": "2018-04-08", "new_device": "f", "users": 274 }, { "day": "2018-04-08", "new_device": "t", "users": 50 }, { "day": "2018-04-09", "new_device": "f", "users": 368 }, { "day": "2018-04-09", "new_device": "t", "users": 66 }, { "day": "2018-04-10", "new_device": "f", "users": 555 }, { "day": "2018-04-10", "new_device": "t", "users": 100 }, { "day": "2018-04-11", "new_device": "f", "users": 501 }, { "day": "2018-04-11", "new_device": "t", "users": 70 }, { "day": "2018-04-12", "new_device": "f", "users": 342 }, { "day": "2018-04-12", "new_device": "t", "users": 45 }, { "day": "2018-04-13", "new_device": "f", "users": 310 }, { "day": "2018-04-13", "new_device": "t", "users": 49 }, { "day": "2018-04-14", "new_device": "f", "users": 317 }, { "day": "2018-04-14", "new_device": "t", "users": 37 }, { "day": "2018-04-15", "new_device": "f", "users": 266 }, { "day": "2018-04-15", "new_device": "t", "users": 42 }, { "day": "2018-04-16", "new_device": "f", "users": 288 }, { "day": "2018-04-16", "new_device": "t", "users": 49 }, { "day": "2018-04-17", "new_device": "f", "users": 303 }, { "day": "2018-04-17", "new_device": "t", "users": 43 }, { "day": "2018-04-18", "new_device": "f", "users": 339 }, { "day": "2018-04-18", "new_device": "t", "users": 48 }, { "day": "2018-04-19", "new_device": "f", "users": 991 }, { "day": "2018-04-19", "new_device": "t", "users": 142 }, { "day": "2018-04-20", "new_device": "f", "users": 752 }, { "day": "2018-04-20", "new_device": "t", "users": 113 }, { "day": "2018-04-21", "new_device": "f", "users": 522 }, { "day": "2018-04-21", "new_device": "t", "users": 65 }, { "day": "2018-04-22", "new_device": "f", "users": 464 }, { "day": "2018-04-22", "new_device": "t", "users": 71 }, { "day": "2018-04-23", "new_device": "f", "users": 574 }, { "day": "2018-04-23", "new_device": "t", "users": 96 }, { "day": "2018-04-24", "new_device": "f", "users": 653 }, { "day": "2018-04-24", "new_device": "t", "users": 122 }, { "day": "2018-04-25", "new_device": "f", "users": 783 }, { "day": "2018-04-25", "new_device": "t", "users": 148 }, { "day": "2018-04-26", "new_device": "f", "users": 1280 }, { "day": "2018-04-26", "new_device": "t", "users": 244 }, { "day": "2018-04-27", "new_device": "f", "users": 550 }, { "day": "2018-04-27", "new_device": "t", "users": 109 }, { "day": "2018-04-28", "new_device": "f", "users": 837 }, { "day": "2018-04-28", "new_device": "t", "users": 295 }, { "day": "2018-04-29", "new_device": "f", "users": 378 }, { "day": "2018-04-29", "new_device": "t", "users": 60 }, { "day": "2018-04-30", "new_device": "f", "users": 389 }, { "day": "2018-04-30", "new_device": "t", "users": 59 }, { "day": "2018-05-01", "new_device": "f", "users": 389 }, { "day": "2018-05-01", "new_device": "t", "users": 52 }, { "day": "2018-05-02", "new_device": "f", "users": 375 }, { "day": "2018-05-02", "new_device": "t", "users": 78 }, { "day": "2018-05-03", "new_device": "f", "users": 374 }, { "day": "2018-05-03", "new_device": "t", "users": 88 }, { "day": "2018-05-04", "new_device": "f", "users": 357 }, { "day": "2018-05-04", "new_device": "t", "users": 66 }, { "day": "2018-05-05", "new_device": "f", "users": 358 }, { "day": "2018-05-05", "new_device": "t", "users": 63 }, { "day": "2018-05-06", "new_device": "f", "users": 341 }, { "day": "2018-05-06", "new_device": "t", "users": 86 }, { "day": "2018-05-07", "new_device": "f", "users": 182 }, { "day": "2018-05-07", "new_device": "t", "users": 66 } ], "app_id": [ "a4097d7796ad512b711c9ea-983eeb40-5831-11e7-5b11-007c928ca240" ], "comment": null, "created_at": "2018-05-07T19:06:07Z", "expires_at": "2018-05-07T20:09:38.457Z", "truncated": false, "query": { "metrics": [ "users" ], "dimensions": [ "day", "new_device" ], "conditions": { "day": [ "between", "2018-04-07", "2018-05-07" ] }, "app_id": [ "a4097d7796ad512b711c9ea-983eeb40-5831-11e7-5b11-007c928ca240" ], "order": [ "+day", "+new_device", "-users" ], "limit": 50000, "translate": {}, "comment": null, "sampling_ratio": 1, "no_aggregate": false }, "_links": { "self": { "href": "/v1/query?app_id%5B%5D=a4097d7796ad512b711c9ea-983eeb40-5831-11e7-5b11-007c928ca240≈p_id%5B%5D=a4097d7796ad512b711c9ea-983eeb40-5831-11e7-5b11-007c928ca240&conditions%5Bday%5D%5B%5D=between&conditions%5Bday%5D%5B%5D=2018-04-07&conditions%5Bday%5D%5B%5D=2018-05-07&dimensions%5B%5D=day&dimensions%5B%5D=new_device&limit=50000&metrics%5B%5D=users&no_aggregate=falseℴ%5B%5D=%2Bdayℴ%5B%5D=%2Bnew_deviceℴ%5B%5D=-users&sampling_ratio=1.0" }, "app": { "href": "/v1/apps/a4097d7796ad512b711c9ea-983eeb40-5831-11e7-5b11-007c928ca240" }, "root": { "href": "/v1" } } }

0
Sriprasad Shivaram Bhat May 18, 2017 at 07:38 AM
3

Hello Apu,

Nothing wrong from your end ,it took a long for me to understand the below behavior :)

This is the known issue/behavior as per my understanding.

While you are converting JSON to XML ,output from convertor needs to be valid XML.If you pass above JSON the XML which will get created is invalid .i.e without root.If you test above JSON in any online tools there is option to add root node which will resolve the above issue.But when it comes to our convertor its not the case.

Solution:

You have to add root element to above JSON before passing it to Convertor.Below code will do the same

Step1 :

Download json-simple-1.1.jar and add it to package src.main.resources.library [ By default it will not be available you need to create it inside your integration package ].

Step 2:

Add below script just before your convertor.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import org.json.simple.*
import groovy.json.*

def Message processData(Message message) {
    //Get Body 
    def body = message.getBody(String.class);

    //Define JSONSlurper
    def jsonSlurper = new JsonSlurper()
    def list = jsonSlurper.parseText(body)
    
    //Define JSONObject and add a root node
    JSONObject myobj = new JSONObject(); 
    myobj.put("Root",list);

    //Format the JSON output
    def jsonOP = JsonOutput.toJson(myobj)
    
    //Set the modified JSON to body and return
    message.setBody(jsonOP);
    return message;
}

Regards,

Sriprasad Shivaram Bhat

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Sriprasad,

Thanks for your prompt reply.

This is definitely a gap in HCI which can be handled easily giving an option - Add wrapper element as it in in the REST adapter of PI. It will be nice if SAP can add same kind of option in JSON to XML converter and reverse in the XML to JSON converter as well.

2 observations -

1) After implementing your suggestion, it's working absolutely fine, but I can see the element sequence is changed in the XML tag after conversion if I compare with JSON -

<Flights>
<price>300</price>
<plane>
<totalSeats>300</totalSeats>
<type>Boeing 777</type>
</plane>
<departureDate>2016-01-20T00:00:00</departureDate>
<origin>MUA</origin>
<ID>3</ID>
<emptySeats>0</emptySeats>
<code>ffee0192</code>
<destination>LAX</destination>
</Flights>

2) In case of mutiple JSON records it's not working. I guess this script is adding root into each record and when there will be multiple records like below, we need to add another Root node to wrap all records.

[
{
"ID": 1,
"code": "rree0001",
"price": 541,
"departureDate": "2016-01-20T00:00:00",
"origin": "MUA",
"destination": "LAX",
"emptySeats": 0,
"plane": {
"type": "Boeing 787",
"totalSeats": 200
}
},
{
"ID": 2,
"code": "eefd0123",
"price": 300,
"departureDate": "2016-01-25T00:00:00",
"origin": "MUA",
"destination": "CLE",
"emptySeats": 7,
"plane": {
"type": "Boeing 747",
"totalSeats": 345
}
}

]

Any suggestion?

Thanks,

Apu

0
avatar image
Former Member May 20, 2017 at 07:29 AM
0

Thanks Sriprasad.Thanks a lot for your quick help.

Share
10 |10000 characters needed characters left characters exceeded
Siby Sabu
Dec 18, 2017 at 08:24 AM
0

Thanks a lot Sriprasad. Solved the issue !!!

Share
10 |10000 characters needed characters left characters exceeded
Florian Kube May 30 at 01:04 PM
0

Thanks. It solved my problem.

Share
10 |10000 characters needed characters left characters exceeded