0

I need to map an incoming EDI document which contains some data in an unusual format. I would like to convert some concatenated data back to the original key-value pairs in the destination document.

Source Data

The original key-value pairs were concatenated into a long string, and then the string has been split across several elements of the EDI document as follows:

MAN PB <OPTION 1 TOKEN> OPTION 1 VALUE <OPTION 2 TOK
MAN PB EN> OPTION 2 VALUE <OPTION 3 TOKEN> OPTION 3 
MAN PB VALUE <OPTION 4 TOKEN> OPTION 4 VALUE <OPTION
MAN PB  5 TOKEN> OPTION 5 VALUE <OPTION 6 TOKEN> OPT
MAN PB ION 6 VALUE

NOTE: the angle brackets are only used to separate the token from the value. This is not XML data encoded into the EDI doc.

Partial Conversion

I have successfully converted the values back to a concatenated string using a Cumulative Concatenate functoid, followed by a script functoid that cleans out a few unwanted characters. I can map the concatenated string to a single field in the destination document:

"<OPTION 1 TOKEN> OPTION 1 VALUE <OPTION 2 TOKEN> OPTION 2 VALUE <OPTION 3 TOKEN> OPTION 3 VALUE <OPTION 4 TOKEN> OPTION 4 VALUE <OPTION 5 TOKEN> OPTION 5 VALUE <OPTION 6 TOKEN> OPTION 6 VALUE"

Desired Result

How can I map this re-assembled string to a loop in the destination document instead?

The destination has a repeating <option> element and I would like the string to be split and mapped so it looks like this:

<item>

  ...some other stuff...

  <option>
    <name>OPTION 1 TOKEN</name>
    <value>OPTION 1 VALUE</value>
  </option>
  <option>
    <name>OPTION 2 TOKEN</name>
    <value>OPTION 2 VALUE</value>
  </option>

  ...remaining options...

</item>

Additional Details

BizTalk doesn't seem to have a string splitting function that I can use here, unless I'm missing something.

I have already created a C# function that can convert the concatenated string into a list of key-value pairs however, I can't figure out how to apply this in BizTalk b/c scripts don't seem to work with iterable return values like List<>, IEnuerable<>, etc.

I also have to run a secondary conversion process on the data using a stored procedure in MSSQL 2008 R2. I would also consider a SQL based solution.

Jens Ehrich
  • 573
  • 1
  • 6
  • 19
  • Have you seen this blog? https://seroter.com/2008/10/07/splitting-delimited-values-in-biztalk-maps/ as mentioned in the answer of this https://stackoverflow.com/questions/23645491/in-biztalk-mapper-how-to-use-split-array-concept – Dijkgraaf May 01 '20 at 04:51
  • The linked blog looks like it should work for this scenario, however, I ended up solving the problem in SQL so I haven't had a chance to test. – Jens Ehrich Jun 30 '20 at 13:49

1 Answers1

-2

You should be able to define this in your schema used to parse the message. That is the easiest and best way to handle - however there

silverbugg
  • 214
  • 2
  • 7