Obtaining usable Json when converting from xml

Question

I am having difficulty getting data from JSON that has been automatically converted from XML and would appreciate expertise to identify the best way to reach my goal.

I have an xml message that i read from a restful service. I am including a shortened anonymized version of the xml message at the bottom of the post for reference. The message is details about a person, and a person can have multiple addresses. Each address can have zero to 6 address lines and if the address line values are not filled in, the system does not include them in the xml document.

I convert it to Json using the json() function. I then parse the json to make the values easily accessible.

Here is where i hit an issue.

When I convert the message to Json using the json() function I get the below Json schema (see bottom of post). Notice that the addresses are not in an array format, in fact none of it is.

Im unsure how to pull values from this in a way that will work for other PersonMessageService messages that are not this specific message.

My questions are:

• Is this automatically produced json clunky and not very usable?
• is there a better way to generate the json from xml in a logic app?
• How do i get the values from my json after conversion?

XML Message

<PersonMessageService version="1.5.3">
<response timestamp="2020-03-31T08:48:42.273557+01:00">
    <status>SUCCESS</status>
    <description/>
    <receipt>A227C1E61EFA2B05E8530100007F6E90</receipt>
</response>
<payload>
    <PersonData>
        <MessageMeta>
            <messageId>123456</messageId>
            <MessageChecksum>c7875a0fd6869bb1234c82bd00712345</MessageChecksum>
        </MessageMeta>
        <personRecord SystemKey="123456789" timestamp="2020-01-31T08:48:35">
            <GUID>9889898</GUID>
            <idNumber>3578951</idNumber>
            <firstName>Joe</firstName>
            <surname>Bloggs</surname>
            <title>Mr</title>
            <gender>M</gender>
            <dateOfBirth>1980-01-01</dateOfBirth>
            <homeAddress addressObjectId="10001" timestamp="2018-11-12T14:58:01">
                <address>
                    <addressLine1>123 Fake St</addressLine1>
                    <country>United States</country>
                </address>
                <email>Joe@bloggs.com</email>
                <mobile>5558878558</mobile>
            </homeAddress>
            <termAddress addressObjectId="10002" timestamp="2018-11-12T15:07:01">
                <address/>
                <email>joebloggs@hotmail.com</email>
            </termAddress>
            <workAddress addressObjectId="10003" timestamp="2018-11-12T15:07:01">
                <address/>
            </workAddress>
            <mailingLabel addressObjectId="10004" timestamp="2018-11-12T14:58:01">
                <address>
                    <addressLine1>58 Fake Bvd</addressLine1>
                    <addressLine2>Fake County</addressLine2>
                    <country>United States</country>
                </address>
            </mailingLabel>
            <referenceNumbers/>
            <personIndicators>
                <country/>
                <nationality/>
                <marketingCorrespondence>N</marketingCorrespondence>
            </personIndicators>
        </personRecord>
    </PersonData>
</payload>

After automatic conversion to JSON using JSON() function

{
  "PersonMessageService": {
    "@version": "1.5.3",
    "response": {
      "@timestamp": "2020-03-31T08:48:42.273557+01:00",
      "status": "SUCCESS",
      "description": null,
      "receipt": "A227C1E61EFA2B05E8530100007F6E90"
    },
    "payload": {
      "PersonData": {
        "MessageMeta": {
          "messageId": "123456",
          "MessageChecksum": "c7875a0fd6869bb1234c82bd00712345"
        },
        "personRecord": {
          "@SystemKey": "123456789",
          "@timestamp": "2020-01-31T08:48:35",
          "GUID": "9889898",
          "idNumber": "3578951",
          "firstName": "Joe",
          "surname": "Bloggs",
          "title": "Mr",
          "gender": "M",
          "dateOfBirth": "1980-01-01",
          "homeAddress": {
            "@addressObjectId": "10001",
            "@timestamp": "2018-11-12T14:58:01",
            "address": {
              "addressLine1": "123 Fake St",
              "country": "United States"
            },
            "email": "Joe@bloggs.com",
            "mobile": "5558878558"
          },
          "termAddress": {
            "@addressObjectId": "10002",
            "@timestamp": "2018-11-12T15:07:01",
            "address": null,
            "email": "joebloggs@hotmail.com"
          },
          "workAddress": {
            "@addressObjectId": "10003",
            "@timestamp": "2018-11-12T15:07:01",
            "address": null
          },
          "mailingLabel": {
            "@addressObjectId": "10004",
            "@timestamp": "2018-11-12T14:58:01",
            "address": {
              "addressLine1": "58 Fake Bvd",
              "addressLine2": "Fake County",
              "country": "United States"
            }
          },
          "referenceNumbers": null,
          "personIndicators": {
            "country": null,
            "nationality": null,
            "marketingCorrespondence": "N"
          }
        }
      }
    }
  }
}

Answer 1

You won't get anything better than this out of an automatic converter - in fact I think it's done quite a good job.

Doing better conversion than this requires human intelligence, in particular, an understanding of the data model. But that means defining your own conversion rules for individual parts of the XML. You can achieve that easily enough with XSLT. The new data types and JSON serialization method make this easier with XSLT 3.0, but it can also be done in 1.0 or 2.0, it just takes a bit more effort.