Skip to Content
Author's profile photo Eng Swee Yeoh

JSONTransformBean Part 1: Converting JSON content to XML

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 /wp-content/uploads/2015/03/out1_665854.png

Screenshots of actual configuration and testing are also shown below.

Module configuration on an SFTP sender channel.

/wp-content/uploads/2015/03/param_665856.png

The ‘oriJSON’ log version shows the original JSON content before conversion.

/wp-content/uploads/2015/03/ori_665857.png

After conversion, the payload changes to XML format.

/wp-content/uploads/2015/03/conv_665860.png

The audit log shows the trace of steps being executed by the module.

/wp-content/uploads/2015/03/log_665859.png

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

Assigned tags

      35 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Great Document

      Author's profile photo Aman Gupta
      Aman Gupta

      Thanks Eng for the wonderful knowledge share

      Regards,

      Aman

      Author's profile photo Former Member
      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      Cool 🙂 .

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

      --Divyesh   

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

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

      Author's profile photo Former Member
      Former Member

      Hello Eng Swee,

      As below screen short i got the response in postman.

      Please suggest.

      Thanks

      Author's profile photo Vipin Kanchan
      Vipin 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 [].

      Author's profile photo Bhargava krishna Talasila
      Bhargava krishna Talasila

      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      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

      Author's profile photo Stefan Bosshard
      Stefan Bosshard

      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

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

      /wp-content/uploads/2015/06/result_733693.png

      Rgds

      Eng Swee

      Author's profile photo Stefan Bosshard
      Stefan Bosshard

      Hi Eng Swee,

      thank you very much for your quick answer!

      Rgds

      stefan

      Author's profile photo Former Member
      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      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

      Author's profile photo Former Member
      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

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

      Best regards,

      Julio Cesar

      Author's profile photo Former Member
      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

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Osman

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

      Rgds

      Eng Swee

      Author's profile photo sp md
      sp md

      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

      Author's profile photo Rashmi Joshi
      Rashmi Joshi

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

      Thanks & Regards,

      Rashmi

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

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

      Author's profile photo Rajesh PS
      Rajesh PS

      Eng Swee 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>"

      Author's profile photo SRIKANTH RATNAM
      SRIKANTH RATNAM

      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

      Author's profile photo marcos mendes
      marcos mendes

      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

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      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.

       

      Author's profile photo Estoppey Quentin
      Estoppey Quentin

      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 ?

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

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

       

      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.

      Author's profile photo Kavita Dubey
      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,

      Author's profile photo zanru ni
      zanru ni

      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

       

      Author's profile photo Dinesh M
      Dinesh M

      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