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

  • pre = saves payload before conversion
  • post = saves payload after conversion
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 /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

To report this post you need to login first.

21 Comments

You must be Logged on to comment or reply to a post.

  1. divyesh vasani

    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

    (0) 
    1. Eng Swee Yeoh 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

      (0) 
        1. Eng Swee Yeoh 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

          (0) 
  2. 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 [].

    (0) 
  3. 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

    (0) 
    1. Eng Swee Yeoh 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

      (0) 
  4. 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

    (0) 
    1. Eng Swee Yeoh 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

      (0) 
  5. Julio Cesar Ruiz

    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

    (0) 
    1. Eng Swee Yeoh 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

      (0) 
      1. Julio Cesar Ruiz

        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

        (0) 
        1. Eng Swee Yeoh 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

          (0) 
  6. Osman Jabri

    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

    (0) 
  7. 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

    (0) 

Leave a Reply