Handle X12 multiples source xml elements to target xml elements

Use BizTalk as processing engine

Photo from: https://www.biztalk360.com/blog/everything-you-need-to-know-about-using-the-biztalk-mapper-tool/

Suppose we have below incoming EDI 830 data:

ISA*00*          *00*          *ZZ*xxxxxxxxxxxxx *ZZ*yyyyyyyyyyyy *240122*1415*U*00401*000000361*0*T*:~
GS*PS*xxxxxxxxxxxxx*yyyyyyyyyyyy*20240122*1415*361*T*004010~
ST*830*0361~
BFR*00*Plan_ABC*240122021501615|1036264002*BB*A*20240122**20240122~
REF*ZZ*Version*20240122141431~
LIN**BP*A1*VP*XYZ-0A1~
UIT*EA~
PID*F****NVDIAxx~
REF*ZZ*CustomerId*ID1~
REF*ZZ*CustomerSiteId*SiteID2~
REF*ZZ*ItemType*3POI~
REF*ZZ*ItemCategory*GPU~
N1*SU*ABC LIMITED~
.....

The mapping structure looks like below, we have multiples REF elements that mapping to our targets elements (these elements present database fields):

Source data content           ====================> target xml elements
REF*ZZ*CustomerId*ID1         --------------------> CUSTOMER_ID
REF*ZZ*CustomerSiteId*SiteID2 --------------------> CUSTOMER_SITE_ID
N/A                           --------------------> TRADING_PARTNER_ID
REF*ZZ*ItemType*3POI          --------------------> ITEM_TYPE
REF*ZZ*ItemCategory*GPU       --------------------> ITEM_CATEGORY

After mapping process completed, we expected that we could get below xml format data:

/*REF mapping outcome*/
<CUSTOMER_ID>ID1</CUSTOMER_ID>
<CUSTOMER_SITE_ID>SiteID2</CUSTOMER_SITE_ID>
<TRADING_PARTNER_ID/>
<ITEM_TYPE>3POI</ITEM_TYPE>
<ITEM_CATEGORY>GPU</ITEM_CATEGORY>

Below was the solution we adopt to map between our source and target schema for the REF session (repeating REF elements).

Map between source EDI xml to target xml

1. Use a “String Concatenate” to concatenate REF02 as key and REF03 as value became pattern as: ‘REF02,REF03;REF02,REF03…’.
2. Use a “Cumulative Concatenate” to cumulate string to “Scripting”.
3. Use “Scripting” to parsing the cumulative string that produced from step2, writing a C# inline code snippet to extra the key/value pair via the token of ‘,’ and ‘;’ that generated under step1.

More detail as below images:

String Concatenate

Cumulative Concatenate

Scripting inputs

Scripting

public string GetData(string SourceData, string Code,int Idx)
{
   string Result = "";
   string[] a = SourceData.Split('；');
   foreach (string word in a)
   {
      string[] b = word.Split('，');
      if (b[0] == Code)
      {
          Result=b[Idx];
          break;
      }
   }
   return Result;
}

Reference

• https://www.biztalk360.com/blog/everything-you-need-to-know-about-using-the-biztalk-mapper-tool/