XML to JSON Control - Why does it leave random XML remnants

I used the XML to JSON control to convert a XML file to JSON format.

I’m expecting the following:

Sample XML snippet

      <policyImageResponse>
         <return>
            <response>
               <RqUID>74d2da6f-6328-45d0-9472-2af93bdec3e5</RqUID>
               <TransactionResponseDt>2018-08-21-04:00</TransactionResponseDt>
            </response>
            <status>
               <statusCd>0</statusCd>
               <statusDesc>Success</statusDesc>
            </status>

         </return>
      </policyImageResponse>

Expected JSON of XML snippet

{
	"policyImageResponse": {
		"return": {
			"response": {
				"RqUID": "74d2da6f-6328-45d0-9472-2af93bdec3e5",
				"TransactionResponseDt": "2018-08-21-04:00"
			},
			"status": {
				"statusCd": "0",
				"statusDesc": "Success"
			}
		}
	}
}

After running the XML to JSON control, I get the following:

{
  "policyImageResponse": [
    {
      "return": [
        {
          "response": [
            {
              "RqUID": [
                {
                  "$xml_": "74d2da6f-6328-45d0-9472-2af93bdec3e5"
                }
              ],
              "TransactionResponseDt": [
                {
                  "$xml_": "2018-08-21-04:00"
                }
              ]
            }
          ],
          "status": [
            {
              "statusCd": [
                {
                  "$xml_": "0"
                }
              ],
              "statusDesc": [
                {
                  "$xml_": "Success"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

As builders, are we expected to write AirScript to remove the extra objects and “xml?” variables?

Or what is the way to solve the issue of an incorrect conversion XML to JSON conversion.

Thanks

This actually looks expected. While this specific situation would allow for the object you described, XML is a very flexible format and you could (in theory) have 2 <return> tags or 2 <status> tags, in which case the JSON representation would have to be an array.

The current implementation is a “lowest common denominator” format that should work for any XML file, so for cases like this it ends up a bit more verbose.

Hey Jeff,

My teammate @kyung wrote a handful of XML related User Defined Functions (UDFs) to make working with XML a little easier. For the XML snippet you provided, you can use this one and it should output a cleaner JSON Object:

XMLOp#ToObject

FROM
  value,
  key
IN
  source
SELECT
  "{{key}}": IF(
    TYPEOF(value) = "list",
    IF(LENGTH(value) = 1, IF(LENGTH(KEYS(value[0])[?(@ = "$xml_")]) = 1, value[0]["$xml_"], XMLOp#ToObject(value[0])), XMLOp#ToList(value)),
    value
  )

Additionally, there are a few more that I’d like to share in case you or anyone in the community needs them!

XMLOp#ToList

FROM
  el
IN
  source
SELECT
  XMLOp#ToObject(el)

XML#Attributes

IF(
  attributes = NULL
    OR LENGTH(attributes) = 0,
  "",
  CONCAT(
    " ",
    JOIN(
      FROM
        attrib
      IN
        attributes
      SELECT
        "{{
          attrib.key
        }}=\"{{
          XML#Escape(attrib.value)
        }}\"",
      " "
    )
  )
)

XML#Build

FIRST(FROM value, key IN source SELECT XML#Node(key, value))

XML#Element

CONCAT(
  "<{{element_name}}",
  XML#Attributes(
    FROM
      value,
      key
    IN
      properties
    WHERE
      TYPEOF(value) <> "list" AND key <> "$xml_"
    SELECT
      { "key": key, "value": value }
  ),
  ">",
  XML#Text(
    FIRST(FROM value, key IN properties WHERE key = "$xml_" SELECT value)
  ),
  JOIN(
    FROM
      value,
      key
    IN
      properties
    WHERE
      TYPEOF(value) = "list"
    SELECT
      XML#Node(key, value),
    ""
  ),
  "</{{element_name}}>"
)

XML#Escape

IF(
  ISSTRING(str),
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(SUBSTITUTE(str, "&", "&amp;"), "'", "&apos;"),
        "\"",
        "&quot;"
      ),
      "<",
      "&lt;"
    ),
    ">",
    "&gt;"
  ),
  str
)

XML#FromAppObject

XML#Build(XML#Transform(source))

XML#InnerTransform

IF(
  TYPEOF(source) = "appobject",
  [
    FROM
      value,
      key
    IN
      source
    SELECT
      "{{
        IF(
          STRING_FIND(key, "@") = 0,
          SUBSTRING(key, 1),
          key = "#text",
          "$xml_",
          key
        )
      }}": IF(
        STRING_FIND(key, "@") = 0 OR key = "#text",
        value,
        XML#InnerTransform(value)
      )
  ],
  TYPEOF(source) = "list",
  FLAT(FROM el IN source SELECT XML#InnerTransform(el)),
  [ { "$xml_": source } ]
)

XML#Node

JOIN(FROM node IN source SELECT XML#Element(name, node), "")


XML#Text

IF(ISSTRING(text), XML#Escape(text), IF(text = NULL), "", text)

XML#Transform

FIRST(XML#InnerTransform(source))

1 Like

Hey @ismaen I was testing this out and wanted to confirm that you need to run the XML to Json Data operation first then one of the above UDFs second. Is that correct?

xml > xml to json data op > udf on the output of the xml to json data op

1 Like

@Drew yup, these are designed to work with the XML to JSON data op

I was able to run the Custom Function and seems to work.