Search code examples
xsdbiztalkbiztalk-2013

Debatching Biztalk Flatfile Schema with nested repeating nodes

I have a flatfile with nested repeating nodes that needs needs to be debatched to single records.

The flatfile:

ABC DEF  1234.456789012345678
000000123456L01   Supplier 0
001000123456L01   00301Address 1                                 
000001234567L01   Supplier 1
001001234567L01   00301Address 2
001001234567L01   00301Address 3
001001234567L01   00302Address 4

Lines starting with 000 are Suppliers, starting with 001 are Supplier addresses. Characters 4-15 are the supplier-code.

Currently I cannot get beyond outputting one record with all addresses per supplier:

<Record>
  <Supplier>
    <Supplier_code>001234567L01</Supplier_code>
    <Supplier_name>Supplier 1</Supplier_name>
  </Supplier>
  <Address>
    <Supplier_address>Address_2</Supplier_address>
    <Supplier_address>Address_3</Supplier_address>
    <Supplier_address>Address_4</Supplier_address>
  </Address>
</Record>

However, the expected output is one record per supplier address:

<Record>
  <Supplier>
    <Supplier_code>001234567L01</Supplier_code>
    <Supplier_name>Supplier 1</Supplier_name>
  </Supplier>
  <Address>
    <Supplier_address>Address_2</Supplier_address>
  </Address>
</Record>

<Record>
  <Supplier>
    <Supplier_code>001234567L01</Supplier_code>
    <Supplier_name>Supplier 1</Supplier_name>
  </Supplier>
  <Address>
    <Supplier_address>Address_3</Supplier_address>
  </Address>
</Record>


<Record>
  <Supplier>
    <Supplier_code>001234567L01</Supplier_code>
    <Supplier_name>Supplier 1</Supplier_name>
  </Supplier>
  <Address>
    <Supplier_address>Address_4</Supplier_address>
  </Address>
</Record>

How would I need to alter my FlatFile schema to accomplish the above?

Solution

  • You can't do this in one pass with the flat file disassembler. There's no way to indicate to it that certain nodes need to be grouped/split/etc. It'd be fairly trivial to do this in a map though. Create a new Schema that's structured like your first, but has an additional root node to allow the Records to repeat, and your map will look like this:

    looping split map sample

    And just make sure MaxOccurs is set properly (probably unbounded) on the Supplier_address element and the destination Record node. Add this map to your receive port and it'll come into the MessageBox split up as desired. Note that you do need a new schema, because you can't have a message with multiple root nodes. If you need to further debatch that, you could set it up as an envelope schema, have a request response port subscribe to it, and use the XML disassembler on the response end to debatch it. Your only other option would be to do this in a custom pipeline component after the flat file dasm has done its magic.