XML to JSON Converter

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.