How to access a multi value XML containing a json string

Question

I have a json string inside a multi value xml tag, which I'm struggling to access. I would like to concatenate the "pid" values to have something like

"listOfPids" : "0000444, 0000111"

<xml>

<custom-attributes>
    <custom-attribute attribute-id="status">
        <value>{
                    "order":"000123"
                }
        </value>
        <value>{
            "prodId": "01010101",
            "status": [
                {
                    "package": "1234",
                    "products": [
                        {
                            "pid": "0000444",
                            "amount": "2"
                        }
                    ]
                },
                {
                    "package": "6789",
                    "products": [
                        {
                            "pid": "0000111",
                            "amount": "5"
                        }
                    ]
                }
            ]
}
        </value>
    </custom-attribute>
</custom-attributes>    
</xml>

I to get at least the first value and tried read and flatten, but didn't get it to work...

listOfPids: read(flatten((payload.xml."custom-attributes".*"custom-attribute") filter ($.@"attribute-id" == "status"))[1].value default "", "application/json").status[0].products[0].pid

score 0 · Answer 1 · answered Aug 03 '21 at 08:11

Your input doesn't seem to be consistent for the value tag, that contains the json that you are interested in. Try with the one mentioned in the answer below:

Input

<?xml version="1.0" encoding="UTF-8"?>
<custom-attributes>
        <custom-attribute attribute-id="status">
            <value>{
                    "order":"000123"
                }
        </value>
            <value>
            {
    "prodId": "01010101",
    "status": [{
            "package": "1234",
            "products": [{
                "pid": "0000444",
                "amount": "2"
            }]
        },
        {
            "package": "6789",
            "products": [{
                "pid": "0000111",
                "amount": "5"
            }]
        }
    ]
}
        </value>
        </custom-attribute>
</custom-attributes>

Script

%dw 2.0
output json
input payload xml
---

listOfPids:(read((payload."custom-attributes"."custom-attribute")[1],"applciation/json").status..products..pid) joinBy ","

Output

{
  "listOfPids": "0000444,0000111"
}

score 0 · Answer 2 · answered Aug 04 '21 at 17:39

This will be dynamic in the fact that there could be more value objects with pid values and as long as they're in the same structure, this script will concatenate them all

%dw 2.0
output application/json

var objectValues = payload.xml.'custom-attributes'.'custom-attribute'.*value

fun convertObjectsToDw(arr: Array<String>) = arr map (objectString) -> read(objectString)
---
{
    listOfPids: ((convertObjectsToDw(objectValues))..status..products..pid) joinBy  ", "
}

Michael Jones · Answer 3 · 2021-08-06T03:49:53.733

%dw 2.0
output application/json
---
listOfPids: (
    payload..*value map read($, "application/json")
)..pid joinBy ","

I see this as more readable and less complicated. As long as the key value is consistently only JSON strings, you can just select all of them and then map them into structured JSON objects, and then select all the pid values and join them.

This also has the advantage that regardless of the depth of the XML doc, or if the structure changes like in one of the answer examples, it just pulls every key called value and pulls out the pids.

How to access a multi value XML containing a json string

3 Answers3