Detect XML attribute xsi:nil and apply null value

[Thom]

I am using a SOAP Execute to retrieve information from an API. The payload is being converted to JSON. The problem is any field that is null returns with an attribute of @xsi:nil:“true”. This is just an example subset. There are actually more than 10 fields returning like this.
[
{
“ns2:communityCodeDesc”: {
“@xmlns:xsi”: “http://www.w3.org/2001/XMLSchema-instance”,
“@xsi:nil”: “true”
},
“ns2:countyCodeDesc”: {
“@xmlns:xsi”: “http://www.w3.org/2001/XMLSchema-instance”,
“@xsi:nil”: “true”
},
“ns2:dateCreated”: “2023-05-16T11:49:32.010-04:00”,
“ns2:dateObsoleted”: {
“@xmlns:xsi”: “http://www.w3.org/2001/XMLSchema-instance”,
“@xsi:nil”: “true”
},
“ns2:familyRSN”: “1250”
}
]
I need this to be in a more standard JSON form like this…
[
{
“communityCodeDesc”: null,
“countyCodeDesc”: null,
“dateCreated”: “2023-05-16T11:49:32.010-04:00”,
“dateObsoleted”: null,
“familyRSN”: “1250”
}
]
So I need the xsi: construct converted to null and the namespace identifier dropped.
Any help would be appreciated.

[JensDeveloper]

Thom:

communityCodeDesc

Hi @Thom

I see that when its null it’s always an object with two properties and when it has a value its type is a string. So I created a pipeline that checks which type it is and based on that it gets the value or puts null as the value. The pipeline below:
Soap_format_2023_06_22.slp (4.6 KB)

Let me know if it helps

Jens

[Thom]

Thanks for this example. I was envisioning something that iterated through all the fields in the document and converting these objects to null. Do you believe that to be possible?
Any number of fields could be null and that could be different in each document being processed.
Thanks again.

[Thom · Accepted solution]

Expanding on the solution provided and using information from the web related to javascript, I came up with the following statement. There was experimentation involved for sure.
$.mapValues((value,key)=> typeof(value) == ‘object’ && value.hasOwnProperty(“@xsi:nil”)? null :value).mapKeys((value,key)=> key.replace(‘ns2:’,‘’))
mapValues and mapKeys are admittedly a bit of a black box for me still.
Thanks @JensDeveloper for leading me done a good path.

[alchemiz]

Hi Thom, just tried another approach using string manipulation 😊

JSON.parse(JSON.stringify($).replace(/ns2:|\"@xmlns:xsi":"http:\/\/www.w3.org\/2001\/XMLSchema-instance"|"@xsi:nil":"true"/gm,'').replace(/\{,\}/gm,'null'))

Just in case a nested object comes along

e.g