Dataweave Payload Transformation - NetSuite SOAP

Question

I have JSON payload which I need to transform and make a call to NetSuite. Payload:

 [{
    "Netsuite Sales Order Id": "13738407",
    "Project Number": "CSPRJ0010592",
    "Project Sys Id": "0863d7141b9861548e835244604btest1",
   "Netsuite Sales Order Line Id": "1",
    "Project Task Number": "CSPRJTASK0322101",
    "Project Task Sys Id": "0f345f141b9861548e8352446TASK1",
    "Update Flag": false
    },
    {
    "Netsuite Sales Order Id":"13738407",
    "Project Number": "CSPRJ0010593",
    "Project Sys Id": "0863d7141b9861548e835244604btest2",
    "Netsuite Sales Order Line Id": "3",
    "Project Task Number": "CSPRJTASK0322102",
    "Project Task Sys Id": "0f345f141b9861548e835244604TASK4",
    "Update Flag": false
    }]

I need to use NetSuite Sales order ID and update the sale order with this payload on Body level field.

Code I used but didn't work:

%dw 2.0
output application/xml
ns ns0 urn:messages_2021_1.platform.webservices.netsuite.com
ns ns01 urn:relationships_2021_1.lists.webservices.netsuite.com
ns ns02 urn:core_2021_1.platform.webservices.netsuite.com
ns ns03 urn:sales_2021_1.transactions.webservices.netsuite.com
ns xsi http://www.w3.org/2001/XMLSchema-instance
---
{
    ns0#update: {
        (payload map((item,index) ->
            ns0#record @(internalId: item."Netsuite Sales Order Id", "xmlns:ns03": ns03, xsi#"type": "ns03:SalesOrder"): {
            ns03#customFieldList: {
                StringCustomFieldRef__custbody_eb_mulesoft_data: {
                    ns02#value: { NetsuiteSalesOrderLineId: item."Netsuite Sales Order Line Id",
                        ProjectSysId: item."Project Sys Id",
                    ProjectNumber: item."Project Number",
                    ProjectTaskNumber: item."Project Task Number",
                    ProjectTaskSysId: item."Project Task Sys Id"
                    
                    }
                    
                }
            }
        }
        ))
    }
}

I need to send whole payload to NetSuite sales order body level field as JSON. Field : custbody_eb_mulesoft_data

Please help.

Output i receive for above code which is the error:

<?xml version='1.0' encoding='UTF-8'?>
<ns0:update xmlns:ns0="urn:messages_2021_1.platform.webservices.netsuite.com">
  <ns0:record internalId="13738407" xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns03:SalesOrder">
    <ns03:customFieldList xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com">
      <StringCustomFieldRef__custbody_eb_mulesoft_data>
        <ns02:value xmlns:ns02="urn:core_2021_1.platform.webservices.netsuite.com">
          <NetsuiteSalesOrderLineId>1</NetsuiteSalesOrderLineId>
          <ProjectSysId>0863d7141b9861548e835244604btest1</ProjectSysId>
          <ProjectNumber>CSPRJ0010592</ProjectNumber>
          <ProjectTaskNumber>CSPRJTASK0322101</ProjectTaskNumber>
          <ProjectTaskSysId>0f345f141b9861548e8352446TASK1</ProjectTaskSysId>
        </ns02:value>
      </StringCustomFieldRef__custbody_eb_mulesoft_data>
    </ns03:customFieldList>
  </ns0:record>
  <ns0:record internalId="13738407" xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns03:SalesOrder">
    <ns03:customFieldList xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com">
      <StringCustomFieldRef__custbody_eb_mulesoft_data>
        <ns02:value xmlns:ns02="urn:core_2021_1.platform.webservices.netsuite.com">
          <NetsuiteSalesOrderLineId>3</NetsuiteSalesOrderLineId>
          <ProjectSysId>0863d7141b9861548e835244604btest2</ProjectSysId>
          <ProjectNumber>CSPRJ0010593</ProjectNumber>
          <ProjectTaskNumber>CSPRJTASK0322102</ProjectTaskNumber>
          <ProjectTaskSysId>0f345f141b9861548e835244604TASK4</ProjectTaskSysId>
        </ns02:value>
      </StringCustomFieldRef__custbody_eb_mulesoft_data>
    </ns03:customFieldList>
  </ns0:record>
</ns0:update>

Notice i have 2 NS records but it should only be 1 record being updated.

Expected output or is there a way i can push the same payload referenced on top?:

<?xml version='1.0' encoding='UTF-8'?>
<ns0:update xmlns:ns0="urn:messages_2021_1.platform.webservices.netsuite.com">
  <ns0:record internalId="13738407" xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns03:SalesOrder">
    <ns03:customFieldList xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com">
      <StringCustomFieldRef__custbody_eb_mulesoft_data>
        <ns02:value xmlns:ns02="urn:core_2021_1.platform.webservices.netsuite.com">
          <NetsuiteSalesOrderLineId>1</NetsuiteSalesOrderLineId>
          <ProjectSysId>0863d7141b9861548e835244604btest1</ProjectSysId>
          <ProjectNumber>CSPRJ0010592</ProjectNumber>
          <ProjectTaskNumber>CSPRJTASK0322101</ProjectTaskNumber>
          <ProjectTaskSysId>0f345f141b9861548e8352446TASK1</ProjectTaskSysId>
          <NetsuiteSalesOrderLineId>3</NetsuiteSalesOrderLineId>
          <ProjectSysId>0863d7141b9861548e835244604btest2</ProjectSysId>
          <ProjectNumber>CSPRJ0010593</ProjectNumber>
          <ProjectTaskNumber>CSPRJTASK0322102</ProjectTaskNumber>
          <ProjectTaskSysId>0f345f141b9861548e835244604TASK4</ProjectTaskSysId>
        </ns02:value>
      </StringCustomFieldRef__custbody_eb_mulesoft_data>
    </ns03:customFieldList>
  </ns0:record>
</ns0:update>

Answer 1

If your object looks like this:

{
  "Payload": [
    {
      "Netsuite Sales Order Id": "15922713",
      "Project Number": "CSPRJ0018639",
      "Project Sys Id": "54d948a11b680690fbe1535b234bcb26",
      "Netsuite Sales Order Line Id": "1",
      "Project Task Number": "CSPRJTASK0858566",
      "Project Task Sys Id": "85d948a11b680690fbe1535b234bcb50"
    },
    {
      "Netsuite Sales Order Id": "15922713",
      "Project Number": "CSPRJ0018639",
      "Project Sys Id": "54d948a11b680690fbe1535b234bcb26",
      "Netsuite Sales Order Line Id": "3",
      "Project Task Number": "CSPRJTASK0858567",
      "Project Task Sys Id": "4ad9c4ad1b280290751bcaab234bcb8a"
    }
  ]
}

then your transformation should look like this:

%dw 2.0
output application/xml
ns ns0 urn:messages_2021_1.platform.webservices.netsuite.com
ns ns01 urn:relationships_2021_1.lists.webservices.netsuite.com
ns ns02 urn:core_2021_1.platform.webservices.netsuite.com
ns ns03 urn:sales_2021_1.transactions.webservices.netsuite.com
ns xsi http://www.w3.org/2001/XMLSchema-instance
---
{
    ns0#update: {
        (payload.Payload map ((item,index) ->
            ns0#record @(internalId: item."Netsuite Sales Order Id", "xmlns:ns03": ns03, xsi#"type": "ns03:SalesOrder"): {
            ns03#customFieldList: {
                StringCustomFieldRef__custbody_eb_mulesoft_data: {
                    ns02#value: { NetsuiteSalesOrderLineId: item."Netsuite Sales Order Line Id",
                        ProjectSysId: item."Project Sys Id",
                    ProjectNumber: item."Project Number",
                    ProjectTaskNumber: item."Project Task Number",
                    ProjectTaskSysId: item."Project Task Sys Id"
                    
                    }
                    
                }
            }
        }
        ))
    }
}

Notice the "payload.Payload"

Answer 2

As long as you are sure that multiple repeated element with the same name are ok, move the map() under ns02#value and make it an object ({...}) so the key-values inside the parenthesis are added inside. I'm assuming the input array has always at least one item, so we can take the internalId from the first item directly.

%dw 2.0
output application/xml
ns ns0 urn:messages_2021_1.platform.webservices.netsuite.com
ns ns01 urn:relationships_2021_1.lists.webservices.netsuite.com
ns ns02 urn:core_2021_1.platform.webservices.netsuite.com
ns ns03 urn:sales_2021_1.transactions.webservices.netsuite.com
ns xsi http://www.w3.org/2001/XMLSchema-instance
---
{
    ns0#update: {
        ns0#record @(internalId: payload[0]."Netsuite Sales Order Id", "xmlns:ns03": ns03, xsi#"type": "ns03:SalesOrder"): {
        ns03#customFieldList: {
            StringCustomFieldRef__custbody_eb_mulesoft_data: 
                ns02#value: {
                    (payload map((item,index) -> {
                        NetsuiteSalesOrderLineId: item."Netsuite Sales Order Line Id",
                        ProjectSysId: item."Project Sys Id",
                        ProjectNumber: item."Project Number",
                        ProjectTaskNumber: item."Project Task Number",
                        ProjectTaskSysId: item."Project Task Sys Id"             
                    }))
                }
            }
        }
        
    }
}

Output:

<?xml version='1.0' encoding='UTF-8'?>
<ns0:update xmlns:ns0="urn:messages_2021_1.platform.webservices.netsuite.com">
  <ns0:record internalId="15922713" xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns03:SalesOrder">
    <ns03:customFieldList xmlns:ns03="urn:sales_2021_1.transactions.webservices.netsuite.com">
      <StringCustomFieldRef__custbody_eb_mulesoft_data>
        <ns02:value xmlns:ns02="urn:core_2021_1.platform.webservices.netsuite.com">
          <NetsuiteSalesOrderLineId>1</NetsuiteSalesOrderLineId>
          <ProjectSysId>54d948a11b680690fbe1535b234bcb26</ProjectSysId>
          <ProjectNumber>CSPRJ0018639</ProjectNumber>
          <ProjectTaskNumber>CSPRJTASK0858566</ProjectTaskNumber>
          <ProjectTaskSysId>85d948a11b680690fbe1535b234bcb50</ProjectTaskSysId>
          <NetsuiteSalesOrderLineId>3</NetsuiteSalesOrderLineId>
          <ProjectSysId>54d948a11b680690fbe1535b234bcb26</ProjectSysId>
          <ProjectNumber>CSPRJ0018639</ProjectNumber>
          <ProjectTaskNumber>CSPRJTASK0858567</ProjectTaskNumber>
          <ProjectTaskSysId>4ad9c4ad1b280290751bcaab234bcb8a</ProjectTaskSysId>
        </ns02:value>
      </StringCustomFieldRef__custbody_eb_mulesoft_data>
    </ns03:customFieldList>
  </ns0:record>
</ns0:update>

If you remove the curly braces in ns02#value the script will generate one ns02#value per item in the input array.