XML to JSON Conversion Issues

Question

Is there a way of converting XML to JSON with reference to a schema.

Lets say a part of the xsd is as below:

  <xs:complexType name="RegionStateResultType">
    <xs:sequence>
      <xs:element name="RegionOrState"     type="xs:string"  minOccurs="0" maxOccurs="1"/>
      <xs:element name="RegionOrStateCode" type="xs:string"  minOccurs="0" maxOccurs="1"/>
      <xs:element name="PinCode" type="xs:integer"  minOccurs="0" maxOccurs="1"/>
    </xs:sequence>
  </xs:complexType>

and the xml is:

<RegionOrStateInfo>
    <RegionOrState>Tochigi</RegionOrState>
    <RegionOrStateCode>09</RegionOrStateCode>
    <PinCode>12345</PinCode>
</RegionOrStateInfo>

On Using XMLtoJSON conversion Policy , if we set RecognizeNumber as 'true' then resulting json is:

{
    "RegionOrState": "Tochigi",
    "RegionOrStateCode": "09" ,
    "PinCode":12345
},
{
    "RegionOrState": "Tokushima",
    "RegionOrStateCode": 36 ,
    "PinCode":12345
}

If RecognizeNumber is set as 'false' then the resulting json is:

{
    "RegionOrState": "Tochigi",
    "RegionOrStateCode": "09" ,
    "PinCode": "12345"
},
{
    "RegionOrState": "Tokushima",
    "RegionOrStateCode": "36" ,
    "PinCode": "12345"
}

The required json is:

{
    "RegionOrState": "Tochigi",
    "RegionOrStateCode": "09" ,
    "PinCode": 12345
},
{
    "RegionOrState": "Tokushima",
    "RegionOrStateCode": "36" ,
    "PinCode": 12345
}

Any way of achieving this?

Has anyone used JSONSchema and is there a way for the converter to refer to a schema and then do the translation?

I also faced some issues with single element arrays in XML to JSON conversion. Thinking is JSONSchema can be of some help

Answer 1

I faced same issue of apigee array handling when array holds only one object. Apigee doesn't maintain xsd so it is difficult for apigee to understand an array when it contains single object. I have added to two javascript policies to resolve the issue.

Policy Number 1:This policy is applied in the flow before converting the xml to json by apigee.

Purpose: Add bank object in each array.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Javascript async="false" continueOnError="false" enabled="true" timeLimit="200" name="Modify-XML-response">
    <DisplayName>Modify XML response</DisplayName>
    <FaultRules/>
    <Properties/>
    <ResourceURL>jsc://modifyXMLResponse.js</ResourceURL>
</Javascript>

modifyXMLResponse.js

Here I am adding a blank Sfiwfalertquerynotes object where the array has at least one Sfiwfalertquerynotes object.

var jsonResponse = response.content;
var returnStr = jsonResponse;
returnStr = returnStr.split("</Sfiwfalertquerynotes>").join("</Sfiwfalertquerynotes><Sfiwfalertquerynotes/>");
response.content = returnStr; 

Policy Number 2:This policy is applied in the flow after converting the xml to json by apigee.

Purpose: Because we added a blank object in the xml arrays when apigee converted the xml to json, each array will have blank object like {} or "". We need to cleanup all these from each array.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Javascript async="false" continueOnError="false" enabled="true" timeLimit="200" name="modifyjsonresponse">
    <DisplayName>Modify Json response</DisplayName>
    <FaultRules/>
    <Properties/>
    <ResourceURL>jsc://modifyJsonResponse.js</ResourceURL>
</Javascript>

modifyJsonResponse.js

var jsonResponse = response.content;
var returnStr = jsonResponse;
returnStr = returnStr.split("},\"\"").join("}")
response.content = returnStr; 

Target Endpoint Preflow:

<PreFlow name="PreFlow">
    <Request>
        <Step>
            <Name>SetTargetURL</Name>
        </Step>
    </Request>
    <Response>
        <Step>
            <Name>Modify-XML-response</Name>
        </Step>
        <Step>
            <Name>XmltoJson</Name>
        </Step>
        <Step>
            <FaultRules/>
            <Condition>(content-type = "application/xml") or (content-type = "text/xml")</Condition>
            <Name>ResponseJsontoXml</Name>
        </Step>
        <Step>
            <Name>modifyjsonresponse</Name>
        </Step>
    </Response>
</PreFlow>

Answer 2

The answer to your question is that there is currently no way of using an xsd file or JSON schema during the XMLToJSON conversion.

You could fix these issues by writing custom code that has knowledge of your payloads (tweaking the types), but that requires a bunch of custom code that I don't like to write.

I fix these issues (forcing numeric/boolean-looking fields to be strings and forcing arrays to be converted to arrays even when they have 0 or 1 elements) using XSL and a JavaScript policy.

1. I add the string ~STR~ to my string values that might look like booleans or numbers (forcing numbers and booleans to not be recognized as such).
2. I also add <element>~ARRAY~</element> twice for each element that is supposed to be an array element (replace "element" in that with the actual element name). This forces at least two elements to exist for each of your arrays, meaning you'll always get an array type. (My XSL collects the true array elements using xsl:for-each.)
3. I'm guessing you want the JSON response to be an unnamed array of objects at the top level. XML doesn't have this concept (there is always a root element). To fix this, I create a root element in my XML named <NOROOT>. This should work for an unnamed array or object at the top level of your JSON.
4. Run XMLToJSON with RecognizeNumber and RecognizeBoolean set to true. The XML will be converted to JSON with the correct data types.

5. Run a JavaScript policy to strip out the hints you added to the XML to get the correct conversion. You can use the same JavaScript policy for all of the XML to JSON conversions you do. Here is my code:

   var jsonResponse = context.getVariable("jsonResponse")
                             .replace(/"~STR~"/, "null")
                             .replace(/~STR~/g, "")
                             .replace(/"~ARRAY~",/g, "")
                             .replace(/"~ARRAY~"/g, "");
   var jsonVar = JSON.parse(jsonResponse);
   var jsonOut = jsonVar.ROOT;
   if (jsonOut.NOROOT !== undefined) {
       jsonOut = jsonOut.NOROOT;
   }
   
   response.content = JSON.stringify(jsonOut);
   

Your example would be changed (prior to calling XMLToJSON) from:

<RegionOrStateInfo>
    <RegionOrState>Tochigi</RegionOrState>
    <RegionOrStateCode>09</RegionOrStateCode>
    <PinCode>12345</PinCode>
</RegionOrStateInfo>
<RegionOrStateInfo>
    <RegionOrState>Tokushima</RegionOrState>
    <RegionOrStateCode>36</RegionOrStateCode>
    <PinCode>12345</PinCode>
</RegionOrStateInfo>

to:

<NOROOT>
    <RegionOrStateInfo>~ARRAY~</RegionOrStateInfo>
    <RegionOrStateInfo>~ARRAY~</RegionOrStateInfo>
    <RegionOrStateInfo>
        <RegionOrState>Tochigi</RegionOrState>
        <RegionOrStateCode>~STR~09</RegionOrStateCode>
        <PinCode>12345</PinCode>
    </RegionOrStateInfo>
    <RegionOrStateInfo>
        <RegionOrState>Tokushima</RegionOrState>
        <RegionOrStateCode>~STR~36</RegionOrStateCode>
        <PinCode>12345</PinCode>
    </RegionOrStateInfo>
</NOROOT>

and the resulting JSON, after conversion and running the JavaScript cleanup function would be (shown pretty-printed below):

[
    {
        "RegionOrState": "Tochigi",
        "RegionOrStateCode": "09",
        "PinCode": 12345
    },
    {
        "RegionOrState": "Tokushima",
        "RegionOrStateCode": "36",
        "PinCode": 12345
    }
]

Note that an array with zero entries would result in [ ], and an array with one entry would be [ {entry} ].

The process looks strange, but it works, and no custom cleanup in JavaScript is required after the conversion.