how to transform given xml to json output?

Question

I have an input payload which is coming as xml. I'm looking for a json output in the format of object as shared below. Input Payload:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
<item>
<item_status>2</item_status>
<item_number>null</item_number>
<details>
<order_flag>
<tag>0000</tag>
<item_line>
<item_quantity>10</item_quantity>
<count>1</count>
</item_line>
<item_line>
<item_quantity>8</item_quantity>
<count>5</count>
</item_line>
</order_flag>
<order_flag>
<tag>1111</tag>
<item_line>
<item_quantity>15</item_quantity>
<count>2</count>
</item_line>
<item_line>
<item_quantity>25</item_quantity>
<count>3</count>
</item_line>
</order_flag>
</details>
</item>

desired Output:

{
  "item_status": "2",
  "item_number": null,
  "order_flag": {
    "tag": "0000",
    "item_line": [
         {
            "item_quantity":"10",
            "count":"1",
            "brand": "null"
         },
                  {
            "item_quantity":"8",
            "count":"5"
            "brand": "null"
         }
         ]
         },
   "order_flag":{
      "tag":"1111",
      "item_line":[
         {
            "item_quantity":"15",
            "count":"2"
            "brand": "null"
         },
                  {
            "item_quantity":"25",
            "count":"3",
            "brand": "null"
         }
         ]
   }   
}

In the incoming payload there is order flag which coming as an array of objects and inside the tag there item line which array of objects too. but the required output should be as above. Thanks for your input

Note that the code format sequence (three backticks) must be in a separate line than your text. — aled, Jul 15 '23 at 01:57

aled · Accepted Answer · 2023-07-15T03:08:24.293

The method I used is to get all the contents of the order_flag elements in an array, using the multi-valued selector, then map to the desired format per element, then use reduce() to convert the array into an object concatenating the items of the array into an object.

%dw 2.0
output application/json
---
{
    item_status: payload.item.item_status,
    item_number: payload.item.item_number,
    ((payload.item.details.*order_flag map
        { 
            order_flag: {
                tag: $.tag,
                item_line: ($.*item_line map ($ ++ {brand: null}))
            }
        }
    ) reduce ((item, acc={})->acc ++ item))
}

Output:

{
  "item_status": "2",
  "item_number": "null",
  "order_flag": {
    "tag": "0000",
    "item_line": [
      {
        "item_quantity": "10",
        "count": "1",
        "brand": null
      },
      {
        "item_quantity": "8",
        "count": "5",
        "brand": null
      }
    ]
  },
  "order_flag": {
    "tag": "1111",
    "item_line": [
      {
        "item_quantity": "15",
        "count": "2",
        "brand": null
      },
      {
        "item_quantity": "25",
        "count": "3",
        "brand": null
      }
    ]
  }
}

Thank you for the quick reply. that sorted out my issue. there is one field "brand" which not coming from input payload, but i will default it to null. how to include that field under the item_line array of object? i edited the field in the post. thank you — james11, Jul 15 '23 at 02:31
Just map the *item_line array to add that field. I'll update my answer. — aled, Jul 15 '23 at 03:07

how to transform given xml to json output?

1 Answers1