XML to JSON Converter

[balu21478963]

Hello Everyone,

Introduction

As we are working on SAP CPI project it is a common requirement to have a message transformation from XML to JSON in our integration flow.

Based on my experience I had a chance to explore streaming feature in standard XML to JSON converter which allows us to generate JSON array with [ ] for child nodes.  Thought why not a have blog post handy to achieve this and share the knowledge to the SAP community, so here it is 😊

Below is the sample XML payload we are going to convert to JSON format

Simple XML payload:

<?xml version="1.0" encoding="UTF-8"?>

<Employee>

	<Record>

		<id>123456</id>

		<gender>M</gender>

		<hours_per_day>7.8</hours_per_day>

		<group_date>2003-01-01</group_date>

		<numero_conge_spectacle/>

		<cell_phone_number>+38017</cell_phone_number>

		<employee>

			<code>FA</code>

			<value>Senior Staff</value>

		</employee>

	</Record>

</Employee>

Expectation is that we suppress root node and require array [] at start of the child node (employee). Below is the expected output.

Expected JSON output:

{

  "Record": {

    "id": "123456",

    "gender": "M",

    "hours_per_day": "7.8",

    "group_date": "2003-01-01",

    "numero_conge_spectacle": "",

    "cell_phone_number": "+38017",

    "employee": [

      {

        "code": "FA",

        "value": "Senior Staff"

      }

    ]

  }

}

The above JSON output can be achieved using standard XML to JSON converter. Below is the basic Iflow

Step1:

Used content modifier to feed the sample XML payload to the IFLOW

IFlow

Step2:

Made use of standard XML to JSON converter. Under the available options in the converter we have streaming option, which needs to be enabled

Step3:

Depending on our output requirement we can select option “ALL” or “Specified ones”

 

• ALL

Selection of ‘ALL’ option allows us to enclose all the elements within square brackets [ ]

{"Record":[{"id":["123456"],"gender":["M"],"hours_per_day":["7.8"],"group_date":["2003-01-01"],"numero_conge_spectacle":[""],"cell_phone_number":["+38017"],"employee":[{"code":["FA"],"value":["Senior Staff"]}]}]}

 

• Specified ones

Our scenario where only the child nodes should have [], we can achieve this by specifying the xpath as shown below

Now we save and deploy the interface to get the output.

Output generated:

{

  "Record": {

    "id": "123456",

    "gender": "M",

    "hours_per_day": "7.8",

    "group_date": "2003-01-01",

    "numero_conge_spectacle": "",

    "cell_phone_number": "+38017",

    "employee": [

      {

        "code": "FA",

        "value": "Senior Staff"

      }

    ]

  }

}

Now let us consider larger XML payload where we have multiple child nodes and every child node require [ ].

This requirement can be achieved by specifying xpath for each child node as shown below

Sample Input:

<?xml version="1.0" encoding="UTF-8"?>

<Employee>

	<Record>

		<id>123456</id>

		<gender>M</gender>

		<hours_per_day>7.8</hours_per_day>

		<group_date>2003-01-01</group_date>

		<numero_conge_spectacle/>

		<cell_phone_number>+38017</cell_phone_number>

		<employee>

			<code>FA</code>

			<value>Senior Staff</value>

		</employee>

		<deactivation_date/>

		<status_marital_name>M</status_marital_name>

		<date_validity_resident_card/>

		<surname>Apple</surname>

		<company>

			<code>999</code>

		</company>

		<email/>

		<address>

			<city>xyz</city>

			<postcode>999</postcode>

			<street_name>abc</street_name>

			<aditionnal_street_name/>

		</address>

		<regime>

			<code>T0R9</code>

			<value>12 abc/xyz</value>

		</regime>

		<contract>

			<code>09</code>

			<start_date>2021-09-01</start_date>

		</contract>

		<status_marital_code>3</status_marital_code>

		<birth>

			<date>1962-01-31</date>

			<country>abc</country>

			<location_city>LMN</location_city>

			<department_code>44</department_code>

			<department>XYZ</department>

		</birth>

		<hours_per_week>39</hours_per_week>

		<nationality>abc</nationality>

		<mainden_name/>

		<authority/>

		<end_date_validity_resident_card/>

		<category>

			<code>1</code>

			<value>Company Employee</value>

		</category>

		<job>

			<code>7161</code>

			<description/>

		</job>

		<employee_type>

			<code/>

		</employee_type>

		<resource_type>

			<code>03</code>

			<value>Staff</value>

		</resource_type>

		<division>

			<code>abc</code>

			<value>xyz</value>

		</division>

		<authorization_number/>

		<staff>YES</staff>

	</Record>

</Employee>

Output generated:

{

  "Record": {

    "id": "123456",

    "gender": "M",

    "hours_per_day": "7.8",

    "group_date": "2003-01-01",

    "numero_conge_spectacle": "",

    "cell_phone_number": "+38017",

    "employee": [

      {

        "code": "FA",

        "value": "Senior Staff"

      }

    ],

    "deactivation_date": "",

    "status_marital_name": "M",

    "date_validity_resident_card": "",

    "surname": "Apple",

    "company": [

      {

        "code": "999"

      }

    ],

    "email": "",

    "address": [

      {

        "city": "xyz",

        "postcode": "999",

        "street_name": "abc",

        "aditionnal_street_name": ""

      }

    ],

    "regime": [

      {

        "code": "T0R9",

        "value": "12 abc/xyz"

      }

    ],

    "contract": [

      {

        "code": "09",

        "start_date": "2021-09-01"

      }

    ],

    "status_marital_code": "3",

    "birth": [

      {

        "date": "1962-01-31",

        "country": "abc",

        "location_city": "LMN",

        "department_code": "44",

        "department": "XYZ"

      }

    ],

    "hours_per_week": "39",

    "nationality": "abc",

    "mainden_name": "",

    "authority": "",

    "end_date_validity_resident_card": "",

    "category": [

      {

        "code": "1",

        "value": "Company Employee"

      }

    ],

    "job": [

      {

        "code": "7161",

        "description": ""

      }

    ],

    "employee_type": [

      {

        "code": ""

      }

    ],

    "resource_type": [

      {

        "code": "03",

        "value": "Staff"

      }

    ],

    "division": [

      {

        "code": "abc",

        "value": "xyz"

      }

    ],

    "authorization_number": "",

    "staff": "YES"

  }

}

As we can see above, we suppressed root node and appended array [] at start of the every child node as xpath is specified in xml element.

Conclusion:

Using streaming option and specifying the xpath to the child node, we can generate JSON array with [ ] for child nodes. 

[yogananda]

Hi balu21478963

Well explained with good example! Keep writing more and more.

[former_member787926]

Thanks for the information keep sharing such informative post keep suggesting such post.

[balu21478963]

Sure, Thank you Yoga

[balu21478963]

Sure Eric

[digirolamocristian60]

Hi Balu,

when I convert xml element into json array and the source xml element has no children, the produced json array is "element":[""]. Why [""] and not just [] ? I always have to fix this with a groovy, which is very annoying.

Thanks in advance!

Cristian

[ekekakos]

I have this XML as output from an RFC FM

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<rfc:Z_CUSTOMER_BALANCE.Response xmlns:rfc="urn:sap-com:document:sap:rfc:functions">
	<ET_CUSTOMERS_BALANCE>
		<item>
			<CUSTOMER>0001000011</CUSTOMER>
			<CUSTOMER_NAME>ELIAS KEKAKOS</CUSTOMER_NAME>
			<CURRENCY>EUR</CURRENCY>
			<BALANCE>39640.8200</BALANCE>
			<MESSAGE/>
		</item>
	</ET_CUSTOMERS_BALANCE>
	<E_BALANCE>39640.8200</E_BALANCE>
	<E_CURRENCY>EUR</E_CURRENCY>
	<E_CUSTNAME>ELIAS KEKAKOS</E_CUSTNAME>
	<E_CUSTOMER>0001000011</E_CUSTOMER>
	<E_RETURN_MESSAGE/>
</rfc:Z_CUSTOMER_BALANCE.Response>

and I am receiving the following JSON

{
    "Z_CUSTOMER_BALANCE.Response": {
        "ET_CUSTOMERS_BALANCE": {
            "item": {
                "CUSTOMER": "0001000011",
                "CUSTOMER_NAME": "ELIAS KEKAKOS",
                "CURRENCY": "EUR",
                "BALANCE": "39640.8200",
                "MESSAGE": ""
            }
        },
        "E_BALANCE": "39640.8200",
        "E_CURRENCY": "EUR",
        "E_CUSTNAME": "ELIAS KEKAKOS",
        "E_CUSTOMER": "0001000011",
        "E_RETURN_MESSAGE": ""
    }
}

I want to have the following output

{
    "Z_CUSTOMER_BALANCE.Response": {
        "ET_CUSTOMERS_BALANCE": {
            "item": [ {
                "CUSTOMER": "0001000011",
                "CUSTOMER_NAME": "ELIAS KEKAKOS",
                "CURRENCY": "EUR",
                "BALANCE": "39640.8200",
                "MESSAGE": ""
            }
           ]
        },
        "E_BALANCE": "39640.8200",
        "E_CURRENCY": "EUR",
        "E_CUSTNAME": "ELIAS KEKAKOS",
        "E_CUSTOMER": "0001000011",
        "E_RETURN_MESSAGE": ""
    }
}

But when I enable STREAMIMG and put in XML ELEMENT the following  Z_CUSTOMER_BALANCE.Response/ET_CUSTOMERS_BALANCE/item nothing is happening and if I have more than 1 ITEMS they appeared as structures like

{
    "Z_CUSTOMER_BALANCE.Response": {
        "ET_CUSTOMERS_BALANCE": {
            "item": {
                "CUSTOMER": "0001000022",
                "CUSTOMER_NAME": "ELIAS KEKAKOS",
                "CURRENCY": "EUR",
                "BALANCE": "238.6900",
                "MESSAGE": ""
            },
            "item": {
                "CUSTOMER": "0001000035",
                "CUSTOMER_NAME": "ELIAS KEKAKOS2",
                "CURRENCY": "EUR",
                "BALANCE": "20639.1000",
                "MESSAGE": ""
            }
      },
        "E_BALANCE": "0.0000",
        "E_CURRENCY": "",
        "E_CUSTNAME": "",
        "E_CUSTOMER": "",
        "E_RETURN_MESSAGE": ""
    }
}

Is there any explanation for this?

Thanks in advance

Elias

[Carme_AG]

Hi, Elias

I have the same issue. Did you solve it?

[ekekakos]

Yes, the answer is in this link CPI XML to JSON . I hope that this will solve your problem.