DataWeave转换NetSuite SOAP：合并订单记录更新自定义字段

原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
    }
]

错误的DataWeave代码 #

%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"
                    }
                }
            }
        }
        ))
    }
}

错误输出 #

<?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>

期望输出 #

<?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>

修复后的DataWeave代码 #

%dw 2.0
output application/xml
ns ns0 urn:messages_2021_1.platform.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 groupBy ($."Netsuite Sales Order Id") pluck ((items, salesOrderId) -> 
            ns0#record @(internalId: salesOrderId, "xmlns:ns03": ns03, xsi#"type": "ns03:SalesOrder"): {
                ns03#customFieldList: {
                    StringCustomFieldRef__custbody_eb_mulesoft_data: {
                        ns02#value: {
                            (items flatMap (item) -> {
                                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"
                            })
                        }
                    }
                }
            }
        ))
    }
}

修复说明 #

1. 分组处理：使用groupBy ($."Netsuite Sales Order Id")将原Payload中同一销售订单ID的记录分组，确保每个销售订单只生成一个<ns0:record>节点。
2. 合并行数据：对每个分组内的记录，用flatMap将所有行的字段展开到同一个<ns02:value>节点下，实现多行数据合并到单个自定义字段中。
3. 移除冗余命名空间：删除了未使用的ns01命名空间，简化代码结构。

内容的提问来源于stack exchange，提问作者Krunal Patel