Update 11 Sep 2015: New parameters escapeInvalidNameStartChar and mangleInvalidNameChar to handle behavior if there are invalid characters for XML element names. Order of fields in XML output now also follows order of fields in JSON input
Update 2 Apr 2015: New parameters allowArrayAtTop and topArrayName to handle JSON Array at top level of JSON content
Update 25 Mar 2015: JSONTransformBeanBean has been refactored to be part of FormatConversionBean. Parameter conversionType replaced with converterClass. Note that some of the screenshots are not updated to reflect the new parameter converterClass
Introduction
With the increase of mobile and cloud solutions, JSON (JavaScript Object Notation) is becoming popular as a lightweight data-interchange format. The file size of data represented in JSON can be significantly smaller than its corresponding representation in XML. Therefore, more and more web services (especially RESTful services) are using JSON as their data interchange format.
Prior to PI/PO 7.4 SP09, there is no native support for conversion of JSON to XML and vice versa. Even with SP09, JSON conversion is limited as a functionality within the new REST adapter, instead of a generic adapter module that can be used with any adapter.
In this two-part series, I will share about JSONTransformBean, a custom adapter module for handling conversion of JSON to XML and back. This module can be used in PI/PO versions older than PI/PO 7.4 SP09 and/or across different adapter types.
This first part covers JSON to XML conversion, while the second part covers XML to JSON conversion.
Source Code
Refer to following blog on location of source code and/or EAR deployment file.
FormatConversionBean – One Bean to rule them all!
This module is based upon the open source JSON in Java library. In order to successfully compile and build this module, the source code for the library needs to be included. Alternatively, the compiled JAR file for the library is also included in the com.equalize.xpi.util library above.
Usage of Module in Communication Channel
Module Processing Sequence
| Number | Module Name | Type | Module Key |
|---|---|---|---|
|
<Depending on position of module in chain> 1) Asynchronous scenario, Normally before the last module in channels 2) Synchronous scenario, Before last module to convert request payload After last module to convert response payload |
Custom_AF_Modules/FormatConversionBean | Local Enterprise Bean | <Any Arbitrary Value> |
Module Parameter Reference
Below are the parameters for configuration of the module for JSON to XML conversion (converterClass = ‘com.equalize.xpi.af.modules.json.JSON2XMLConverter’). Certain parameters will automatically inherit the default values if it is not configured.
| Parameter Name | Allowed values | Default value | Remarks |
|---|---|---|---|
| conversionType | JSON2XML | Required field. Determines conversion type | |
| converterClass | com.equalize.xpi.af.modules.json.JSON2XMLConverter | Required field. Determines conversion class | |
| documentName | Required field. Document name of root element of XML output | ||
| documentNamespace | Required field. Namespace of root element of XML output | ||
| indentFactor | Integer values beginning from 1 | 0 | Determines the number of indentation spaces for each level in the XML output |
| allowArrayAtTop | Y, N | N | Determines if a JSON Array at the top level (input beginning with [) is allowed |
| topArrayName | Name of the top level JSON Array. Required field when allowArrayAtTop = ‘Y’ | ||
| escapeInvalidNameStartChar | N |
First character of XML element name is validated. Character is escaped if it is not a valid name start character. Examples: 64bit –> __64bit, $money –> __u0024money |
|
| mangleInvalidNameChar | N |
Second and subsequent characters of XML element name are validated. Characters are mangled using the corresponding unicode number they are not valid name character. Example: field[a] –> field__u005ba__u005d |
|
| messageLog |
pre, post |
Saves a log version of the message that is viewable in Message Monitor
|
|
| logLocation | Name of log version when messageLog is populated. Location defaulted to value in messageLog if logLocation not populated |
Example
Module parameters
| Parameter Name | Parameter Value |
|---|---|
| converterClass | com.equalize.xpi.af.modules.json.JSON2XMLConverter |
| documentName | MT_JSON2XML |
| documentNamespace | urn:equalize:com |
| indentFactor | 2 |
Result
| Input |
{ “glossary”: { “title”: “example glossary”, “GlossDiv”: { “title”: “S”, “GlossList”: { “GlossEntry”: { “ID”: “SGML”, “SortAs”: “SGML”, “GlossTerm”: “Standard Generalized Markup Language”, “Acronym”: “SGML”, “Abbrev”: “ISO 8879:1986”, “GlossDef”: { “para”: “A meta-markup language, used to create markup languages such as DocBook.”, “GlossSeeAlso”: [“GML”, “XML”] }, “GlossSee”: “markup” } } } } } |
| Output |  |
Screenshots of actual configuration and testing are also shown below.
Module configuration on an SFTP sender channel.
The ‘oriJSON’ log version shows the original JSON content before conversion.
After conversion, the payload changes to XML format.
The audit log shows the trace of steps being executed by the module.
Conclusion
With JSONTransformBean, we are able to handle now services that provide content in JSON format.
Upcoming will be the second part of this series covering XML to JSON conversion with JSONTransformBean. Watch this space!
Second part is now out at JSONTransformBean Part 2: Converting XML to JSON content
Great Document
Thanks Eng for the wonderful knowledge share
Regards,
Aman
Superb one:)
If I want to try, is it just a matter of downloading EAR file(from the link in your blog) and deploy directly? I mean if EAR file is deploy ready. I am working on PO 7.3.1 SP 14
Also, your code supports attribute as well – while doing conversion from JSON to XML. If yes then how it should be configured at adapter level?
–Divyesh
Hi Divyesh
Your version is higher than the compiled version (NWDS 7.31 SP13) so yes, you can deploy the EAR file directly. A few others have done so successfully and here.
As for attributes, I guess you are referring to XML attributes – then no, at the moment it only supports normal XML fields. You can try and model your DT with normal XML fields, and then have a message mapping to map it to attributes.
Rgds
Eng Swee
Cool 🙂 .
Sure, that is good way. I appreciate contribution as it really saves time for integration people.
–Divyesh
Hi Divyesh
Let me elaborate further on why I’ve decided not to support XML attributes for the initial version of the JSON converter.
JSON and XML are two different formats and they cannot be compared on a 1-to-1 basis objectively. There is no concept of “attributes” in the JSON format. Therefore when converting JSON to/from XML, there is no single “rule” as to which JSON content/field should be an XML attribute. There are various different interpretation of what a JSON representation of XML attributes should be like – check out one such discussion below
JSON corresponding to an XML with attributes? – Stack Overflow
In the context of PI, I have chosen to design the converter based on similar concept as flat file conversion via FCC. For FCC, we need to model the Data Type based on what the FCC generated output should be like. Note that FCC also does not support XML attributes. Similarly, the approach for using this converter would be to model the DT based on the output that can/will be generated by the converter. Further mappings on this generated XML can be performed in PI if the final intended output requires XML attributes.
Hope that clarifies things.
Thanks again for your feedback. Do let me know how the deployment of the EAR went, and if the converter is working well. 😉
Rgds
Eng Swee
Hi Eng Swee,
i have deployed custom adapter module(EAR file) in PI system(7.4 dual stack) for JSON to xml conversion but i am facing issue whenever i am testing my scenario.
Scenario(Synchronous): PROXY to REST, requirement JSON to XML conversion for response message.
Response JSON Structure:
{“BrowsePlanCategory”:”[{\”id\”:1,\”Description\”:\”Mobile\”},{\”id\”:2,\”Description\”:\”DTH\”},{\”id\”:3,\”Description\”:\”DataCard\”}]”,”Operator”:null,”Region”:null,”DenominationCategory”:null,”Plans”:null}
Require XML:
<?xml version=“1.0” encoding=“UTF-8”?> <root> <BrowsePlanCategory>[{“id”:1,”Description”:”Mobile”},{“id”:2,”Description”:”DTH”},{“id”:3,”Description”:”DataCard”}]</BrowsePlanCategory> <DenominationCategory null=“true” /> <Operator null=“true” /> <Plans null=“true” /> <Region null=“true” /> </root>
receiver channel screen short :
Error Screen Short:
Please Help me it is urgent requirement.
Thanks
regards
Jatin
You will need to check your JSON structure. Doesn’t seem right.
Hello Eng Swee,
As below screen short i got the response in postman.
Please suggest.
Thanks
Hi Eng Swee,
just wanted to know whether this converter can be used to force PI to send objects as arrays (with a []) even if there is only one value for the object.
If there are multiple values, SAP PI Rest adapter automatically adds the [] with the {}, but if there is only one value, SAP PI REST adapter adds only the {} and does not send the [].
Hi Eng Swee,
Great blog 🙂 , thank you very much for sharing this info.
We have a requirement to convert JSON to XML and we are on 7.0 version.
Can we use this module in PI 7.0? if not do we have any alternate approaches to use this module.
Regards
Bhargava Krishna
Hi Bhargava
This converter is part of FormatConversionBean and was developed for PI 7.3x/7.4. The blog below shows how you can recompile it for other versions of PI.
Recompile com.equalize.xpi.af.modules as EJB 2.1 modules in NWDS 7.1x
However, there are significant changes in the adapter framework API from 7.0 to 7.1x and above, so you will need to get the corresponding 7.0 libraries and adjust accordingly. Also, you will need to do it on a compatible version of NWDS.
Rgds
Eng Swee
Excellent blog and modul – thank you so much for sharing!
I do have a question about following requirement (array in array) – see example below:
{“query”:{“resultCode”:0,”columns”:[“id”,”number”,”language”],
“table”:[[“1″,”1234″,”DE”],[“2″,”5678″,”DE”]]
}}
Is that possible to convert from jsonToXml do get a proper xml structure with subelements and so on?
Thanks and regards
Stefan
Hi Stefan
Thank you for your feedback.
Regarding your question, unfortunately, the module will not be able to convert the JSON array-in-array structure that you have. I’ve tried this on the Advantco REST adapter and it also does not work.
The reason is because JSON and XML are separate specifications and there is not always a 1-1 representation between them. If your example, the two arrays ([“1″,”1234″,”DE”],[“2″,”5678″,”DE”]) that are inside the “table” array do not have an immediate name assigned to it, therefore they behave like unnamed XML elements, which are invalid.
If you want to be able to convert it, the JSON structure should have a name for each of the array – like below, where they belong to a “field” each.
{“query”:{“resultCode”:0,”columns”:[“id”,”number”,”language”],
“table”:[{“field”:[“1″,”1234″,”DE”]},{“field”:[“2″,”5678″,”DE”]}]
}}
The result would like as follows:-
Rgds
Eng Swee
Hi Eng Swee,
thank you very much for your quick answer!
Rgds
stefan
Hello Eng Swee,
this a great module, thank you very much for sharing, I just implemented it and eliminated the “A JSONObject text must begin with ‘{‘ at carácter 1……..” error due to start and ending brackets ‘[‘ ‘]’.
I have a requirement where a need to include the JSON message as a string in a field of the new XML structure as the first field of the XML message just above the first root element, I used the log feature and is great for monitoring and troubleshooting in PI but the JSON needs to be mapped as a reponse to source system, is this something you included in your adapter module?
Thanks in advance,
Regards,
Julio Cesar
Hi Julio Cesar
Thank you for your comment.
Regarding your requirement, I don’t really understand it. Can you further elaborate especially your point below:-
JSON needs to be mapped as a reponse to source system,
If you can share the source and expected target output, it would be helpful.
Rgds
Eng Swee
Hello Eng Swee,
thank you for your quick response, I will try to be clearer this time, what I need is to convert a JSON message to XML message and include the whole source JSON message in a string field of the target XML message as the first field. The following is a sample of the source JSON message:
[
{
“companyName”: “Company 1”,
“identifier”: “XXXXXXXXXXXXXX”,
“changeDate”: “03/14/2015”,
“status”: “Published”,
“communityName”: “TSMS”,
“country”: “XX”
},
{
“companyName”: “Company 2”,
“identifier”: “XXXXXXXXXXX”,
“changeDate”: “03/14/2015”,
“status”: “Published”,
“communityName”: “TSMS”,
“country”: “XX”
}
]
This is the target XML message that I want to get:
<?xml version=”1.0″ encoding=”UTF-8″ ?>
<ns1:MT_Suppliers_Response xmlns:ns1=”http://Suppliers“>
<JSONMessage>[
{
“companyName”: “Company 1”,
“identifier”: “XXXXXXXXXXXXXX”,
“changeDate”: “03/14/2015”,
“status”: “Published”,
“communityName”: “TSMS”,
“country”: “XX”
},
{
“companyName”: “Company 2”,
“identifier”: “XXXXXXXXXXX”,
“changeDate”: “03/14/2015”,
“status”: “Published”,
“communityName”: “TSMS”,
“country”: “XX”
}
]</JSONMessage>
<record>
<companyName>Company 1</companyName>
<identifier>XXXXXXXXXXXXXX</identifier>
<changeDate>03/14/2015</changeDate>
<status>Published</status>
<communityName>TSMS</communityName>
<country>XX</country>
</record>
<record>
<companyName>Company 2</companyName>
<identifier>XXXXXXXXXXX</identifier>
<changeDate>03/14/2015</changeDate>
<status>Published</status>
<communityName>TSMS</communityName>
<country>XX</country>
</record>
</ns1:MT_Suppliers_Response>
The field “JSONMessage” contains the whole JSON message as a string, I hope this definition is better, thank you for your time.
Best regards,
Julio Cesar
Hi Julio Cesar
Ok, I understand your requirement now. So, in addition to the converted XML, you also want to include the original JSON string.
This is quite an uncommon requirement, so unfortunately, the current logic for the JSON2XML converter does cater for this. An option for you would be to download the source code and enhance the module converter according to your specific requirement.
Rgds
Eng Swee
Thank you Eng Swee, I will do that, thanks again for all help and support.
Best regards,
Julio Cesar
Hi Eng Swee
I am not very much familiar with REST Adapter but in see there is inbuilt feature of converting xml to Json and vise versa which i tried also and it worked, then why do we need JsonTransformationBean??
Regards
Osman
Hi Osman
Please read the second and third paragraph of the Introduction section above. I’ve mentioned it there.
Rgds
Eng Swee
Hi Eng,
This is really a helpful blog.
I need help on my requirement:
REST sends data in a encrypted format and in PI we need to decrypt to get JSON (using key provided by REST) and after that from JSON to XML cponversion is needed.
I made a choice to write java mapping for same because this adapter module only supports JSON to XML (my requirement is to decrypt first and do json to xml)
Now, in Java mapping I have the code for decrypt, but after this I am unable to get the code for JSON to XML.
If you can help with the proper jars and code , that helps.
System: PI 7.4 dual stack
SP16
OpenJDK Runtime Environment (2.6.1.3)
Regards,
SP
Very informative document.. I will also try this on test server. Thanks for sharing.
Thanks & Regards,
Rashmi
Hi,
I have a problem with conversion on REST Receiver adapter, method GET. The communication channel is not converting the json payload to html, so the payload that returns to S4Hana is empty.
The interface is sycronous: SAP S4Hana (Proxy) -> SAP PO (REST) -> External System (GET method).
Here is the json returned from external system.
{“Details”:{“OrderDetails”:[{“OrderID”:”0″,”OrderNo”:”00010″ }],”CustomerID”:”00001″,”UserID”:””,”Name”:””,”PCode”:””,”Status”:”Sucess”,”Message”:”Record Inserted Successfully”}}
Here is the error message from PO system.
MP: exception caught with cause com.sap.aii.adapter.rest.ejb.parse.InvalidJSonContent: Invalid JSON message content used; Message: “JSONArray text must end with ] at character 28 of
Bellow are the images.
Channel
Error Message
Please suggest me to resolve this error.
Regards
Surender
Your error is related to the JSON converter in the standard REST adapter. It’s not relevant to this blog.
Hi Eng swee,
The senario is JSON2XML. Below is the strucuture.
{“stores”:[{“storeId”:”9999993″,”fuelProducts”:[{“name”:”UNL”,”effectiveDate”:”2017-10-20T13:50:08.467″,”prices”:[{“serviceMode”:”Self”,”mop”:”Cash”,”price”:2.34900},{“serviceMode”:”Self”,”mop”:”Credit”,”price”:2.34900},{“serviceMode”:”Mini”,”mop”:”Cash”,”price”:2.34900},{“serviceMode”:”Mini”,”mop”:”Credit”,”price”:2.34900},{“serviceMode”:”Full”,”mop”:”Cash”,”price”:2.34900},{“serviceMode”:”Full”,”mop”:”Credit”,”price”:2.34900}]},{“name”:”MID”,”effectiveDate”:”2017-10-20T13:50:08.913″,”prices”:[{“serviceMode”:”Self”,”mop”:”Cash”,”price”:2.56900},{“serviceMode”:”Self”,”mop”:”Credit”,”price”:2.56900},{“serviceMode”:”Mini”,”mop”:”Cash”,”price”:2.56900},{“serviceMode”:”Mini”,”mop”:”Credit”,”price”:2.56900},{“serviceMode”:”Full”,”mop”:”Cash”,”price”:2.56900},{“serviceMode”:”Full”,”mop”:”Credit”,”price”:2.56900}]},{“name”:”PRE”,”effectiveDate”:”2017-10-20T13:50:09.177″,”prices”:[{“serviceMode”:”Self”,”mop”:”Cash”,”price”:3.00900},{“serviceMode”:”Self”,”mop”:”Credit”,”price”:3.00900},{“serviceMode”:”Mini”,”mop”:”Cash”,”price”:3.00900},{“serviceMode”:”Mini”,”mop”:”Credit”,”price”:3.00900},{“serviceMode”:”Full”,”mop”:”Cash”,”price”:3.00900},{“serviceMode”:”Full”,”mop”:”Credit”,”price”:3.00900}]},{“name”:”DSL”,”effectiveDate”:”2017-10-20T13:50:09.513″,”prices”:[{“serviceMode”:”Self”,”mop”:”Cash”,”price”:3.23900},{“serviceMode”:”Self”,”mop”:”Credit”,”price”:3.23900},{“serviceMode”:”Mini”,”mop”:”Cash”,”price”:3.23900},{“serviceMode”:”Mini”,”mop”:”Credit”,”price”:3.23900},{“serviceMode”:”Full”,”mop”:”Cash”,”price”:3.23900},{“serviceMode”:”Full”,”mop”:”Credit”,”price”:3.23900}]}]}]}
Can we convert this using SFTP – module configuration?
If yes do we need to create ESR objects?
Regards
Srikanth