Flat-File Schema – Only Returning One Row

A post on the BizTalk General MSDN Newsgroup relating to flat-file schemas recently caught my eye, primarily because I was working on a bitch of a flat-file schema at the time, but also because I’m looking to give something back to the BizTalk community as a whole.

The post went something along the lines of:

I have an XML schema that I am populating from a dataset:

<Schema>
<NewDataSet>
<Table>
<Field1>
<Field2>
<Field3>

I get multiple rows back from the database so that it looks like this:

<NewDataSet>
<Table>
<Field1>aaa</Field1>
<Field2>bbb</Field2>
<Field3>ccc</Field3>
</Table>
<Table>
<Field1>xxx</Field1>
<Field2>yyy</Field2>
<Field3>zzz</Field3>
</Table>
</NewDataSet>

All seems OK, until I try and map this to a flat file schema – then I only get one row in my resulting file. The schema for the flat file looks like:

<Schema>
<Root>
<Field1>
<Field2>
<Field3>

The fix was quick and simple – all the poster needed to do was insert a new element to hold his data, and tell the parser that the element could repeat, as follows:

[Schema]
<Root> <- Delimited (0x0D 0x0A Hex, Postfix)
<Record> <- Positional/Delimited, 'Max Occurs' = Unbounded
<Field1 />
<Field2 />
<Field3 />
</Record>
<Root>

As a general rule of thumb, its always a good idea to test flat-file schemas before attempting to map them (it helps to stop the headaches when things go wrong). I’ve found that the best way is to generate an XML representation of the schema and use that as an input message to test various scenarios (e.g. repeating elements, data content etc.):

1. Right-click the schema and select ‘Properties’;

2. Change the ‘Generate Instance Output Type’ property to ‘XML’ and click apply;

3. Right-click the schema again and select ‘Generate Instance’, this will generate an XML instance of your schema – modify to your heart’s content!

To test the (modified?) XML instance against your schema:

1. Right-click the schema and select ‘Properties’;

2. Change the ‘Validate Instance Output Type’ property to ‘XML’ and click apply;

3. Set the XML instance filename in the ‘Input Instance Filename’ property and click apply;

4. Right-click the schema again and select ‘Validate Instance’, this will validate your XML instance against the schema;