BizTalk mapping: how to extract single row from looping but, possibly, to extend it to few destination fields?

Question

I have the following issue. In BizTalk core (XML) I have a tag which may be replicated few times. It's cardinality is n, let's say - in theory 0 ≤ n ≤ 99. Let's call it Note[n].

In outgoing file (it's flat) I have five (exactly five) appropriate fields. Let's call them FTX1..5. So what I need to do is to map Note[1] (if it exists) to FTX1, Note[2] (if it exists) to FTX2, ..., Note[5] (if it exists) to FTX5.

But it's not the end. The second part of this task is more complicated.

Each FreeText has a limit 70 characters. But each Note in theory has no limits. So, in the most simple case, when length of all Notes is less than 70 chars, I should just map Note[1..5] to FTX1..5. But. If Note[1] contains, for example, 200 chars, I should map first 70 to FTX1, next 70 to FTX2, last 60 to FTX3. Then I should take Note[2], map to FTX4 and Note[3] to FTX5 (if both are less than 70). The rest should be ignored. If Note[1] contains, for example, 400 chars, I should map first 350 into FTX1..5, truncating last 50, and ignore all other Notes. And so on.

Is it possible to do this using only Functoids? I would appreciate any advice.

Answer 1

You need to start if with Cumulative Concatenate functoid, that will add the Note nodes together.

After that if you simply want to split the string you can use the Left and String Extract functoids to chop up the string.

For the following input

<ns0:Root xmlns:ns0="http://Scratch.SO65382125in">
  <Note>This in note number 1, that needs to have some length.</Note>
  <Note>This in note number 2, that needs to have some length.</Note>
  <Note>This in note number 3, that needs to have some length.</Note>
</ns0:Root>

You get this output

<ns0:Root xmlns:ns0="http://Scratch.SO65382125out">
    <FreeText1>This in note number 1, that needs to have some length.This in note num</FreeText1>
    <FreeText2>ber 2, that needs to have some length.This in note number 3, that need</FreeText2>
    <FreeText3>s to have some length.</FreeText3>
    <FreeText4/>
    <FreeText5/>
</ns0:Root>

Note that is has run the sentences together and also chopped words in two. The first is an easy fix by adding a space constant to the Concatenate functoid before the Cumulative Concatenate functoid.

Output

<ns0:Root xmlns:ns0="http://Scratch.SO65382125out">
    <FreeText1>This in note number 1, that needs to have some length. This in note nu</FreeText1>
    <FreeText2>mber 2, that needs to have some length. This in note number 3, that ne</FreeText2>
    <FreeText3>eds to have some length. </FreeText3>
    <FreeText4/>
    <FreeText5/>
</ns0:Root>

To avoid chopping words apart would be more complex and you would probably have to use scripting functoids.