Technical Articles
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
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.
Hi Balu Badugu
Well explained with good example! Keep writing more and more.
Sure, Thank you Yoga
Thanks for the information keep sharing such informative post keep suggesting such post.
Sure Eric
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