JSONTransformBean Part 1: Converting JSON content to XML

[engswee]

Update 4 Sep 2018: Now available in CPI as well.

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
converterClass	PI - com.equalize.xpi.af.modules.json.JSON2XMLConverter CPI -com.equalize.converter.core.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 pre = saves payload before conversion post = saves payload after conversion Available only in PI
logLocation			Name of log version when messageLog is populated. Location defaulted to value in messageLog if logLocation not populated. Available only in PI

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

[former_member186576]

Great Document

[#3]

Thanks Eng for the wonderful knowledge share

Regards,

Aman

[Former Member]

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

[engswee]

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 here 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

[Former Member]

Cool :smile: .

Sure, that is good way. I appreciate contribution as it really saves time for integration people.

--Divyesh   

[engswee]

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. :wink:

Rgds

Eng Swee

[vipinkumar_kanchan]

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 [].

[Bhargavakrishna]

Hi Eng Swee,

Great blog :smile: , 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

[engswee]

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

[stefan_bosshard2]

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

[engswee]

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

[stefan_bosshard2]

Hi Eng Swee,

thank you very much for your quick answer!

Rgds

stefan

[Former Member]

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

[engswee]

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

[Former Member]

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

[engswee]

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

[Former Member]

Thank you Eng Swee, I will do that, thanks again for all help and support.

Best regards,

Julio Cesar

[Former Member]

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

[engswee]

Hi Osman

Please read the second and third paragraph of the Introduction section above. I've mentioned it there.

Rgds

Eng Swee

[shabbir_mohmad]

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

[rasjoshi]

Very informative document.. I will also try this on test server. Thanks for sharing.

Thanks & Regards,

Rashmi

[surender_durgam]

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

 

[former_member221434]

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

[engswee]

You will need to check your JSON structure. Doesn't seem right.

[engswee]

Your error is related to the JSON converter in the standard REST adapter. It's not relevant to this blog.

[former_member221434]

Hello Eng Swee,

As below screen short i got the response in postman.



Please suggest.

Thanks

[former_member195135]

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

[mlmendes]

Hi Swee,

I've one doubt about,what adapter type to use in the configuration channel. could you show me some print about it?

I'm using the follow configuration, but I'm not sure if is correct.



my scenario its to create conversion xml to json and vice-versa, if you show me this part in CC i'll appreciate it.

 

Thanks in advance!

Marcos Mendes

 

[engswee]

The choice of adapter is up to you and dependent on your integration requirement. This module can be used in any Java-based adapter.

 

Everything else related to configuration is clearly provided in the example on this blog post.

 

[quentin_estoppey]

Hey ! I tried to import your code from Git and in NWDS 7.5 SP 14 but there is errors in MessageDispatcher and MessageLoggerHelper (No such class in new 7.50 libs). Do you have an updated version for Netweaver 7.50 ?

[engswee]

Please follow steps detailed in https://blogs.sap.com/2016/01/22/setup-comequalizexpiafmodules-adapter-module-project-in-nwds-easily...

 

In particular, item 2 in Prerequisite sections tells you the additional JAR files that you need to obtain from the PI system since they are not included in NWDS.

[kavita_dubey]

Hi Eng Swee,

I am planning to use your JSON2XML conversion bean. I have a requirement where i get json content as single record in individual files to covert to XML and sent to Proxy. However, for data load  we have same multiple records in one file to upload to SAP. I am also planning to use your module in a generic scenarios with sender interface signature as "0..unbound" [where, for normal execution it gets one transaction record in a file however, for data load, it gets multiple transaction records in one file]

Can you suggest if your JSON2XML adapter module will handle to convert XML for both cases? Is it possible to achieve this using your exiting code?

Or Can i use  an additional code using Java/XSLT mapping to handle automatic segments of Message and Message1 in structure, also using your adapter module?

Sample Normal daily execution file content:

{
"username" : "user1",
"email" : "user1@mail.com
"
}

Sample Data load file content

[
{ "username" : "user1",
"email" : "user1@mail.com "
},
{ "username" : "user2",
"email" : "user2@mail.com "
}
]

Your help and suggestion will be highly appreciated

Regards,

[former_member633171]

engswee.yeoh But could you please suggest on above error using standard REST Adapter.

 

MP: exception caught with cause com.sap.aii.adapter.rest.ejb.parse.InvalidJSonContent: Invalid JSON message content used; Message: "Input is not valid JSON at character 12 of <html><head>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
<title>502 Server Error</title>
</head>
<body text=#000000 bgcolor=#ffffff>
<h1>Error: Server Error</h1>
<h2>The server encountered a temporary error and could not complete your request.<p>Please try again in 30 seconds.</h2>
<h2></h2>
</body></html>"

[former_member756098]

Hi Eng Swee，

I have a question about JSON2XMLConverter，Have you considered ignoring special ASCII character processing in JSON2XMLConverter, such as 0x9a.
For example: in response have follow error:

An invalid XML character (Unicode: 0x9a) was found in the element content of the document.at com.sap.aii.adapter.soap.web.SOAPHandler.processSOAPtoXMB

 

[dineshhome1361_7]

Hi Eng,

One query, We have standard palette to convert JSON to XML and this custom code.

Could you let me know if there are any advantage of using this Bean or when we should go for this bean than standard palatte in CPI

Thanks,

Dinesh

[former_member739891]

Hi SP,

I have same requirements as you mentioned in PO 7.5. Can you please help me share with your solution.
1. How to receive encrypted JSON data using REST sender channel.
2. How to create Java Mapping to decrypt and convert to XML

Thanks & Regards,

Pampa