[CPI] JSON to XML using groovy

[prabhu_s2]

Hi

Considering the structure of the data that needs to be mapped to the receiver, i need to use a groovy script to create the xml from JSON. While some part works, the looping of the values in the list seem to an issue.

Below the groovy script:

import groovy.json.JsonSlurper 

def body = '[ { "data": { "isDataTrue": true, "totals": [ { "values": [ "30889.63", "305.8379207920792" ] } ], "rowCount": 39, "minimums": [ { "values": [ "1", "0.0" ] } ], "rows": [ { "dimensions": [ " Visitor", "Organic ", "www.compass.com/en/my-account/addresses" ], "metrics": [ { "values": [ "1", "1", "1", "0.0","0.0" ] } ] }, { "dimensions": [ " Visitor", "Non-Organic ", "www.compass.com/en/my-account/addresses" ], "metrics": [ { "values": [ "1", "0", "1", "3.0", "0.0" ] } ] } ], "maximums": [ { "values": [ "5082", "5080" ] } ] }, "columnHeader": { "metricHeader": { "metricHeaderEntries": [ { "name": "ga:sessions", "type": "INTEGER" }, { "name": "ga:users", "type": "INTEGER" }, { "name": "ga:pageviews", "type": "INTEGER" }, { "name": "ga:entrances", "type": "INTEGER" }, { "name": "ga:bounceRate", "type": "PERCENT" } ] }, "dimensions": [ "ga:userType", "ga:cat", "ga:Grouping" ] }, "nextPage": "32000" } ]'


    def msg=body.substring(1, body.length()- 1);		
    def list = new JsonSlurper().parseText(msg);
      
    println(list)
		
	if ( list.data != null ){
	     def val=list.data;
	     String val_str = val.rows;
         String prnt = val_str.substring(1,val_str.length()- 1);
         println(prnt);
         println('--------- mannual xml generation --------');
         Integer i = 1; 
         String mvxml = ''; String rxml = ''; String mdata = '';
         String rdata = '';
         rxml = '<rows>';
         for( def item : val.rows){
             rdata = '<row>'
             
            for(def metrics : item.metrics.values){
                 mvxml = '<m' + i + '>';
                 mvxml = mvxml + metrics[i] + '</m' + i +'>';
                 mdata = mdata + mvxml;
                 i=i+1;
             }
             rdata = rdata + mdata + '</row>';
         }
        if(i > 1){
           rxml = rxml + rdata + '</rows>';
           println(rxml);
        }
    }
	else{

         
    }

I'm getting the following xml:

<rows>
 <row>
  <m1>1</m1>
  <m2>1</m2>
 </row>
</rows>

But what i want to have it:

<rows>
 <row>
  <m1>1</m1>
  <m2>1</m2>
  <m3>1</m3>
  <m4>0.0</m4>
  <m5>0.0</m5>  
 </row>
 <row>
  <m1>1</m1>
  <m2>0</m2>
  <m3>1</m3>
  <m4>3.0</m4>
  <m5>0.0</m5>  
 </row>
</rows>

Not sure on how to read the list values in the element metrics. Any help on this please? or suggestion on how to approach it?

[MortenWittrock]

I've updated my answer with a small XSLT stylesheet, which will map the output of the JSON to XML converter to the format you need.

Regards,

Morten

[ArindamSAP]

From SAP help on JSON to XML conberter.

If you need to convert a JSON file that doesn't fulfil this requirement, you can do the following:

Add a content modifier before the JSON to XML converter that changes the message body. In the entry field in tab Message Body, enter:

{"root": ${in.body}}