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>

What do you mean by saying that you want to "combine" description, ordercode and version? Do yoy want them in same tag comma separated? Or do you want two elements od description? Can you please update the question and add the expected output too? It is very unclear without that. — Harshank Bansal
Also I can see SOURCE_TRANSACTION_ID is also having two different values in both the G_1. What do you want that value when the two G_1s are merged into each other? — Harshank Bansal

George George · Accepted Answer · 2020-10-05T21:42:59

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>

Reformat XML payload by combining fields

1 Answers