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.