Reformat XML payload by combining fields

Question

I have an incoming payload that looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<DATA_DS>
    <G_1>
        <ORDERED_QTY>1</ORDERED_QTY>
        <ORIGINAL_SOURCE_ORDER_NUMBER>ANAND1212</ORIGINAL_SOURCE_ORDER_NUMBER>
        <SOURCE_TRANSACTION_ID>12</SOURCE_TRANSACTION_ID>
        <SOURCE_TRANSACTION_SYSTEM>XYZ</SOURCE_TRANSACTION_SYSTEM>
        <REQUESTOR_NAME>Mickey Mouse</REQUESTOR_NAME>
        <CONTACT_EMAIL>[email protected]</CONTACT_EMAIL>
        <CREATED_BY_EMAIL>[email protected]</CREATED_BY_EMAIL>
        <ORDER_TYPE_CODE>SRO</ORDER_TYPE_CODE>
        <HDR_ORDER_TYPE>Single Recipient</HDR_ORDER_TYPE>
        <ORDER_DATE>2020-09-16T17:06:06.000+00:00</ORDER_DATE>
        <RECIPIENT_CNT>2</RECIPIENT_CNT>
        <ORDERCODE>INT-FIRST</ORDERCODE>
        <VERSION>AB</VERSION>
        <DESCRIPTION>Description for INT_FIRST version AB</DESCRIPTION>
        <CANCELLATION_REASON>Inventory No Stock</CANCELLATION_REASON>
    </G_1>
    <G_1>
        <ORDERED_QTY>1</ORDERED_QTY>
        <ORIGINAL_SOURCE_ORDER_NUMBER>ANAND1212</ORIGINAL_SOURCE_ORDER_NUMBER>
        <SOURCE_TRANSACTION_ID>11</SOURCE_TRANSACTION_ID>
        <SOURCE_TRANSACTION_SYSTEM>XYZ</SOURCE_TRANSACTION_SYSTEM>
        <REQUESTOR_NAME>Mickey Mouse</REQUESTOR_NAME>
        <CONTACT_EMAIL>[email protected]</CONTACT_EMAIL>
        <CREATED_BY_EMAIL>[email protected]</CREATED_BY_EMAIL>
        <ORDER_TYPE_CODE>SRO</ORDER_TYPE_CODE>
        <HDR_ORDER_TYPE>Single Recipient</HDR_ORDER_TYPE>
        <ORDER_DATE>2020-09-16T16:52:32.000+00:00</ORDER_DATE>
        <RECIPIENT_CNT>2</RECIPIENT_CNT>
        <ORDERCODE>INT-SECOND</ORDERCODE>
        <VERSION>CD</VERSION>
        <DESCRIPTION>Description for INT-SECOND version CD</DESCRIPTION>
        <CANCELLATION_REASON>Inventory No Stock</CANCELLATION_REASON>
    </G_1>
    <G_1>
        <ORDERED_QTY>3</ORDERED_QTY>
        <ORIGINAL_SOURCE_ORDER_NUMBER>ANOTHER234</ORIGINAL_SOURCE_ORDER_NUMBER>
        <SOURCE_TRANSACTION_ID>11</SOURCE_TRANSACTION_ID>
        <SOURCE_TRANSACTION_SYSTEM>XYZ</SOURCE_TRANSACTION_SYSTEM>
        <REQUESTOR_NAME>Minnie Mouse</REQUESTOR_NAME>
        <CONTACT_EMAIL>[email protected]</CONTACT_EMAIL>
        <CREATED_BY_EMAIL>[email protected]</CREATED_BY_EMAIL>
        <ORDER_TYPE_CODE>SRO</ORDER_TYPE_CODE>
        <HDR_ORDER_TYPE>Single Recipient</HDR_ORDER_TYPE>
        <ORDER_DATE>2020-09-16T16:52:32.000+00:00</ORDER_DATE>
        <RECIPIENT_CNT>1</RECIPIENT_CNT>
        <ORDERCODE>INT-FIRST</ORDERCODE>
        <VERSION>AB</VERSION>
        <DESCRIPTION>Description for INT-FIRST version AB</DESCRIPTION>
        <CANCELLATION_REASON>Discontinued</CANCELLATION_REASON>
    </G_1>
</DATA_DS>

I need to combine the ORDERCODE, VERSION and DESCRIPTION fields for all G_1 entries that have the same ORIGINAL_SOURCE_ORDER_NUMBER. In the example above, the first two G_1s would be combined and the third one would be separate. All of the other fields from the first G_1 in the sequence would be fine, but I need all of the three fields listed above from each G_1.

My preferred combination would be like below with a new ITEMS element.

<G_1>
    <ORDERED_QTY>1</ORDERED_QTY>
    <ORIGINAL_SOURCE_ORDER_NUMBER>ANAND1212</ORIGINAL_SOURCE_ORDER_NUMBER>
    <SOURCE_TRANSACTION_ID>12</SOURCE_TRANSACTION_ID>
    <SOURCE_TRANSACTION_SYSTEM>XYZ</SOURCE_TRANSACTION_SYSTEM>
    <REQUESTOR_NAME>Mickey Mouse</REQUESTOR_NAME>
    <CONTACT_EMAIL>[email protected]</CONTACT_EMAIL>
    <CREATED_BY_EMAIL>[email protected]</CREATED_BY_EMAIL>
    <ORDER_TYPE_CODE>SRO</ORDER_TYPE_CODE>
    <HDR_ORDER_TYPE>Single Recipient</HDR_ORDER_TYPE>
    <ORDER_DATE>2020-09-16T17:06:06.000+00:00</ORDER_DATE>
    <RECIPIENT_CNT>2</RECIPIENT_CNT>
    <ITEMS>
        <ITEM>
            <ORDERCODE>INT-FIRST</ORDERCODE>
            <VERSION>AB</VERSION>
            <DESCRIPTION>Description for INT_FIRST version AB</DESCRIPTION>
        </ITEM>
        <ITEM>
            <ORDERCODE>INT-SECOND</ORDERCODE>
            <VERSION>CD</VERSION>
            <DESCRIPTION>Description for INT-SECOND version CD</DESCRIPTION>
        </ITEM>
    </ITEMS>
    <CANCELLATION_REASON>Inventory No Stock</CANCELLATION_REASON>
</G_1>

Answer 1

You got to provide the sample output you expect, I assume you want to generate JSON

%dw 2.0
output application/json
---
payload.DATA_DS.*G_1 groupBy $.ORIGINAL_SOURCE_ORDER_NUMBER

There is no ORDERCODE field in your input sample data.

Once you clarify, someone should be able to address any outstanding issues.

EDIT: OK I have corrected the expression to reflect the explanations you provided and my new found eye-sight :D

Try this expression:

%dw 2.0
output application/xml
// Get an array with all the G_1 values
var grouppedData = payload.DATA_DS.*G_1
  // Group the data by the original source order number 
  groupBy $.ORIGINAL_SOURCE_ORDER_NUMBER
  // Get just the values
  pluck $
---
// Iterate over every single unique original source order number
DATA_DS: grouppedData reduce (e,acc={}) -> do {
    // Create a local variable to store the common fields
    var common = e[0] -- ["ORDERCODE","VERSION","DESCRIPTION"]
    // Create a local variable to store the items
    var items = e reduce (item, items={}) -> (
        items ++ {item: {
            ORDERCODE: item.ORDERCODE,
            VERSION: item.VERSION,
            DESCRIPTION: item.DESCRIPTION
        }}
    )
    ---
    acc ++ {GS_1: common ++ items: items}
}

Ignore any errors in the preview, they are false positives.

This expression returns the following output:

<?xml version='1.0' encoding='UTF-8'?>
<DATA_DS>
  <GS_1>
    <ORDERED_QTY>1</ORDERED_QTY>
    <ORIGINAL_SOURCE_ORDER_NUMBER>ANAND1212</ORIGINAL_SOURCE_ORDER_NUMBER>
    <SOURCE_TRANSACTION_ID>12</SOURCE_TRANSACTION_ID>
    <SOURCE_TRANSACTION_SYSTEM>XYZ</SOURCE_TRANSACTION_SYSTEM>
    <REQUESTOR_NAME>Mickey Mouse</REQUESTOR_NAME>
    <CONTACT_EMAIL>[email protected]</CONTACT_EMAIL>
    <CREATED_BY_EMAIL>[email protected]</CREATED_BY_EMAIL>
    <ORDER_TYPE_CODE>SRO</ORDER_TYPE_CODE>
    <HDR_ORDER_TYPE>Single Recipient</HDR_ORDER_TYPE>
    <ORDER_DATE>2020-09-16T17:06:06.000+00:00</ORDER_DATE>
    <RECIPIENT_CNT>2</RECIPIENT_CNT>
    <CANCELLATION_REASON>Inventory No Stock</CANCELLATION_REASON>
    <items>
      <item>
        <ORDERCODE>INT-FIRST</ORDERCODE>
        <VERSION>AB</VERSION>
        <DESCRIPTION>Description for INT_FIRST version AB</DESCRIPTION>
      </item>
      <item>
        <ORDERCODE>INT-SECOND</ORDERCODE>
        <VERSION>CD</VERSION>
        <DESCRIPTION>Description for INT-SECOND version CD</DESCRIPTION>
      </item>
    </items>
  </GS_1>
  <GS_1>
    <ORDERED_QTY>3</ORDERED_QTY>
    <ORIGINAL_SOURCE_ORDER_NUMBER>ANOTHER234</ORIGINAL_SOURCE_ORDER_NUMBER>
    <SOURCE_TRANSACTION_ID>11</SOURCE_TRANSACTION_ID>
    <SOURCE_TRANSACTION_SYSTEM>XYZ</SOURCE_TRANSACTION_SYSTEM>
    <REQUESTOR_NAME>Minnie Mouse</REQUESTOR_NAME>
    <CONTACT_EMAIL>[email protected]</CONTACT_EMAIL>
    <CREATED_BY_EMAIL>[email protected]</CREATED_BY_EMAIL>
    <ORDER_TYPE_CODE>SRO</ORDER_TYPE_CODE>
    <HDR_ORDER_TYPE>Single Recipient</HDR_ORDER_TYPE>
    <ORDER_DATE>2020-09-16T16:52:32.000+00:00</ORDER_DATE>
    <RECIPIENT_CNT>1</RECIPIENT_CNT>
    <CANCELLATION_REASON>Discontinued</CANCELLATION_REASON>
    <items>
      <item>
        <ORDERCODE>INT-FIRST</ORDERCODE>
        <VERSION>AB</VERSION>
        <DESCRIPTION>Description for INT-FIRST version AB</DESCRIPTION>
      </item>
    </items>
  </GS_1>
</DATA_DS>