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

score 2 · Answer 1 · answered Feb 25 '14 at 19:14

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.

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).
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.)
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.
Run XMLToJSON with RecognizeNumber and RecognizeBoolean set to true. The XML will be converted to JSON with the correct data types.

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.

Thanks Mike.But this isn't for one field that i used as sample but for almost all operations (some hundreds of fields).Do you think we should even do that replacement , or send all as string in json and treat it as untyped as it is going to be extremely difficult to maintain. If this needs to be done for almost everything is it better to be handled at client app? — rpattnaik, Feb 26 '14 at 05:50
@rpattnaik Unless you're defining what all of the data types will look like as part of the API's response, it may make sense for the client app to check the value's type instead of assuming. This way the app can check if it's integer, string, array, etc. and then do what's required without encountering problems while extracting/parsing data. This may not be optimal for all scenarios, though. — Michael Russo, Feb 26 '14 at 06:27
I used this where I needed to use XSL to change the format anyway, and it performed well, even on a big payload with hundreds of elements. The best part was that there was no custom programming required -- find the string fields and add ~STR~ to the XSL, and you're done. For the simple payload you show above, I probably wouldn't use XSL -- it would be easy to write a few lines of JavaScript to do what you want. As an app developer, I could see using Michael's defensive programming. As an API developer, though, I would never send the wrong data types in my JSON responses if I could avoid it. — Mike Dunker, Feb 26 '14 at 08:43

score 2 · Answer 2 · answered Apr 23 '14 at 15:59

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>

XML to JSON Conversion Issues

2 Answers2

modifyXMLResponse.js

modifyJsonResponse.js

Target Endpoint Preflow:

Linked