如何循环内部循环以从 XML 映射到 CSV

Question

<?xml version="1.0"?>
<tXML>
<Header>
<Source>1</Source>
<Action_Type>Update</Action_Type>
<Sequence_Number>0</Sequence_Number>
<Batch_ID>4</Batch_ID>
<Reference_ID>043</Reference_ID>
<User_ID>S</User_ID>
<Password>password</Password>
<Message_Type></Message_Type>
<Company_ID>J1</Company_ID>
<Msg_Locale>English (United States)</Msg_Locale>
<Msg_Time_Zone>Eastern Standard Time</Msg_Time_Zone>
<Version></Version>
<Internal_Reference_ID></Internal_Reference_ID>
<Internal_Date_Time_Stamp></Internal_Date_Time_Stamp>
<External_Reference_ID></External_Reference_ID>
<External_Date_Time_Stamp></External_Date_Time_Stamp>
</Header>
<Message>
<DistributionOrder>
<LineItem>
<DoLineNbr>1</DoLineNbr>
<ItemName>135465</ItemName>
<Description>A</Description>
<UpdateActionType></UpdateActionType>
<PackageType></PackageType>
<DoLineStatus>Released</DoLineStatus>
<InventoryAttributes>
<InventoryType>F</InventoryType>
<ProductStatus></ProductStatus>
<BatchNbr></BatchNbr>
<CountryOfOrigin></CountryOfOrigin>
<ItemAttribute1>R</ItemAttribute1>
<ItemAttribute2></ItemAttribute2>
<ItemAttribute3></ItemAttribute3>
<ItemAttribute4></ItemAttribute4>
<ItemAttribute5></ItemAttribute5>
</InventoryAttributes>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>20</NoteCode>
<CommentText>[JPY_ _Mens_ _ _ _ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>13</NoteCode>
<CommentText>[ _00016000.00000_.00000_.00000_ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>SC</NoteType>
<NoteCode>02</NoteCode>
<CommentText>[ _ _ _ _R]</CommentText>
<Visibility>0</Visibility>
</Comment>
</LineItem>
<LineItem>
<DoLineNbr>2</DoLineNbr>
<ItemName>4550155140404</ItemName>
<Description>AS LAMBSWOOL VNECK</Description>
<UpdateActionType></UpdateActionType>
<PackageType></PackageType>
<DoLineStatus>Released</DoLineStatus>
<InventoryAttributes>
<InventoryType>F</InventoryType>
<ProductStatus></ProductStatus>
<BatchNbr></BatchNbr>
<CountryOfOrigin></CountryOfOrigin>
<ItemAttribute1>R</ItemAttribute1>
<ItemAttribute2></ItemAttribute2>
<ItemAttribute3></ItemAttribute3>
<ItemAttribute4></ItemAttribute4>
<ItemAttribute5></ItemAttribute5>
</InventoryAttributes>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>20</NoteCode>
<CommentText>[JPY_ _Mens_ _ _ _ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>13</NoteCode>
<CommentText>[ _00016000.00000_.00000_.00000_ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>SC</NoteType>
<NoteCode>02</NoteCode>
<CommentText>[ _ _ _ _R]</CommentText>
<Visibility>0</Visibility>
</Comment>
</LineItem>
</DistributionOrder>
</Message>
</tXML>

我的代码看起来像这样

%dw 2.0
output application/csv header = false , separator = "|" , quoteValues = false
---
payload.tXML.Message.*DistributionOrder map ((DistributionOrder , indexofDistributionOrder) ->  {
column_1: "000000001",
column_2: "0",
column_3: "0",
column_4: (LineItem.LineProcessInfo.LineRefTextField2 splitBy "_")[0] replace "[" with "" default "",
column_5: (LineItem.LineProcessInfo.LineRefTextField2 splitBy "_")[1] replace "[" with "" default "",
column_6: ????
column_7: "",
column_8: "",
column_9: "",
column_10: "",
})

因此，在上面的代码中，我正在循环每个行项以获取字段，但是在每个行项中，我必须为每个注释循环，并且对于每个注释，我需要将其放在不同的行中。例如，输出应如下所示，如您在示例中看到的那样，如果行项目 2 有 3 个注释部分，我有 3 行用于 3 个注释。

00000002|0001333006|20191220|34621|SAPTOMIF4|CommentText1|||6500055464123|252||J1||||MW09449| 
00000002|0001333006|20191220|34621|SAPTOMIF4|CommentText2|||6500055464123|252||J1||||MW09449| 
00000002|0001333006|20191220|34621|SAPTOMIF4|CommentText3|||6500055464123|252||J1||||MW09449|  
000000003|0001333006|20191220|34621|SAPTOMIF6|CommentText1|||6500055654123|4542||J1||||MW09449|
000000003|0001333006|20191220|34621|SAPTOMIF6|CommentText2|||6500055654123|4542||J1||||MW09449|
000000003|0001333006|20191220|34621|SAPTOMIF6|CommentText3|||6500055654123|4542||J1||||MW09449|
000000004|0001333006|20191220|34621|SAPTOMIF6||||65000545664123|35463||J1||||MW09449| 

Answer 1

您可以尝试在映射中执行映射。但是，如果您希望将结果对象写为 csv，则需要将其平展，因为它将生成数组中的数组。

请参阅下面的示例代码：

%dw 2.0
output application/csv header = false , separator = "|" , quoteValues = false
var payload = read("<tXML>
<Header>
<Source>1</Source>
<Action_Type>Update</Action_Type>
<Sequence_Number>0</Sequence_Number>
<Batch_ID>4</Batch_ID>
<Reference_ID>043</Reference_ID>
<User_ID>S</User_ID>
<Password>password</Password>
<Message_Type></Message_Type>
<Company_ID>J1</Company_ID>
<Msg_Locale>English (United States)</Msg_Locale>
<Msg_Time_Zone>Eastern Standard Time</Msg_Time_Zone>
<Version></Version>
<Internal_Reference_ID></Internal_Reference_ID>
<Internal_Date_Time_Stamp></Internal_Date_Time_Stamp>
<External_Reference_ID></External_Reference_ID>
<External_Date_Time_Stamp></External_Date_Time_Stamp>
</Header>
<Message>
<DistributionOrder>
<LineItem>
<DoLineNbr>1</DoLineNbr>
<ItemName>135465</ItemName>
<Description>A</Description>
<UpdateActionType></UpdateActionType>
<PackageType></PackageType>
<DoLineStatus>Released</DoLineStatus>
<InventoryAttributes>
<InventoryType>F</InventoryType>
<ProductStatus></ProductStatus>
<BatchNbr></BatchNbr>
<CountryOfOrigin></CountryOfOrigin>
<ItemAttribute1>R</ItemAttribute1>
<ItemAttribute2></ItemAttribute2>
<ItemAttribute3></ItemAttribute3>
<ItemAttribute4></ItemAttribute4>
<ItemAttribute5></ItemAttribute5>
</InventoryAttributes>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>20</NoteCode>
<CommentText>[JPY_ _Mens_ _ _ _ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>13</NoteCode>
<CommentText>[ _00016000.00000_.00000_.00000_ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>SC</NoteType>
<NoteCode>02</NoteCode>
<CommentText>[ _ _ _ _R]</CommentText>
<Visibility>0</Visibility>
</Comment>
</LineItem>
<LineItem>
<DoLineNbr>2</DoLineNbr>
<ItemName>4550155140404</ItemName>
<Description>AS LAMBSWOOL VNECK</Description>
<UpdateActionType></UpdateActionType>
<PackageType></PackageType>
<DoLineStatus>Released</DoLineStatus>
<InventoryAttributes>
<InventoryType>F</InventoryType>
<ProductStatus></ProductStatus>
<BatchNbr></BatchNbr>
<CountryOfOrigin></CountryOfOrigin>
<ItemAttribute1>R</ItemAttribute1>
<ItemAttribute2></ItemAttribute2>
<ItemAttribute3></ItemAttribute3>
<ItemAttribute4></ItemAttribute4>
<ItemAttribute5></ItemAttribute5>
</InventoryAttributes>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>20</NoteCode>
<CommentText>[JPY_ _Mens_ _ _ _ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>MB</NoteType>
<NoteCode>13</NoteCode>
<CommentText>[ _00016000.00000_.00000_.00000_ _ _ _ _ _ _ _ ]</CommentText>
<Visibility>0</Visibility>
</Comment>
<Comment>
<NoteType>SC</NoteType>
<NoteCode>02</NoteCode>
<CommentText>[ _ _ _ _R]</CommentText>
<Visibility>0</Visibility>
</Comment>
</LineItem>
</DistributionOrder>
</Message>
</tXML>", "application/xml")
---
flatten(payload.tXML.Message.DistributionOrder.*LineItem map (LineItem) -> 
LineItem.*Comment map (Comment) ->
{
column_1: "000000001", 
column_2: "0", 
column_3: "0", 
column_4: (LineItem.LineProcessInfo.LineRefTextField2 splitBy "_")[0] replace "[" with "" default "", 
column_5: (LineItem.LineProcessInfo.LineRefTextField2 splitBy "_")[1] replace "[" with "" default "", 
column_6: Comment.CommentText, 
column_7: "", 
column_8: "", 
column_9: "", 
column_10: ""
})

这将导致

000000001|0|0|||[JPY_ _Mens_ _ _ _ _ _ _ _ _ _ _ ]||||
000000001|0|0|||[ _00016000.00000_.00000_.00000_ _ _ _ _ _ _ _ ]||||
000000001|0|0|||[ _ _ _ _R]||||
000000001|0|0|||[JPY_ _Mens_ _ _ _ _ _ _ _ _ _ _ ]||||
000000001|0|0|||[ _00016000.00000_.00000_.00000_ _ _ _ _ _ _ _ ]||||
000000001|0|0|||[ _ _ _ _R]||||