Mule 4: Transforming SQL XML object to JSON

Question

I am getting SQLXML resultset as an output of MS SQL stored procedure called from the Database Execute Stored Procedure shape.

The SQLXML resultset looks like:

<xmlcontent>
   <field1>val1</field1>
   <field2>val2</field2>
   <field3>val3</filed3>
</xmlcontent>
<xmlcontent>
   <field1>val1</field1>
   <field2>val2</field2>
   <field3>val3</filed3>
</xmlcontent>
<xmlcontent>
   <field1>val1</field1>
   <field2>val2</field2>
   <field3>val3</filed3>
</xmlcontent>

However, the payload is a bit weird, as it contains the XML output of length 2033 and then repeated as the array element until the payload is exhausted. The transform shape output with the just payload shows:

{
  "resultSet1": [
    {
      "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "<xmlcontent><field1/><field2/><field3/>" --this content is of size 2033 max
    },
    {
      "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "</xmlcontent><xmlcontent><field1/><fiel" --this content is of size 2033 max
    },
    {
      "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "d2/><field3/><xmlcontent>"
    }
  ]
}

I am thinking of using another transform shape to append the values of "XML_F52E2B61-18A1-11d1-B105-00805F49916B" from the resultset1 array and then transforming to JSON. Is there a way in data weave to simplify this approach to get final JSON output as below?

    [
   {
      "field1": "val1",
      "field2": "val2",
      "field3": "val3"
   },
   {
      "field1": "val1",
      "field2": "val2",
      "field3": "val3"
   },
   {
      "field1": "val1",
      "field2": "val2",
      "field3": "val3"
   }
]

Answer 1

Input:

{
  "resultSet1": [
    {
      "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "<xmlcontent><field1>val1</field1><field2>val2</field2><field3>val3</field3>" 
    },
    {
      "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "</xmlcontent><xmlcontent><field1>val1</field1><fiel" 
    },
    {
      "XML_F52E2B61-18A1-11d1-B105-00805F49916B": "d2>val2</field2><field3>val3</field3></xmlcontent>"
    }
  ]
}

Script:

%dw 2.0
output application/json
var id='XML_F52E2B61-18A1-11d1-B105-00805F49916B'
var body=payload.resultSet1 map (
    $[id]) reduce ((item, accumulator) -> accumulator ++ item)

---
read('<root>' ++ body ++ '</root>', "application/xml").root.*xmlcontent

Output:

[
  {
    "field1": "val1",
    "field2": "val2",
    "field3": "val3"
  },
  {
    "field1": "val1",
    "field2": "val2",
    "field3": "val3"
  }
]