Biztalk schema variable number of columns in delimited file permitted?

Question

Hiyas. I have a customer sending orders to us in a flat file. There isn't really any complexity to the file but there are some inconsistencies from file to file.

The format of the file is like this:

1,2,3[CRLF]
1,2,3[CRLF]

No problems creating a schema around that structure, however from time to time they will add a new column.

1,2,3,4[CRLF]
1,2,3,4[CRLF]

Unfortunatly, they don't make their changes cascade backwards so we hare expected to support both the 3 and 4 column formats. Both formats can potentially come through the same pipeline, so i don't really have the option of creating seperate schemas/pipelines. They do always add the new fields to the end of the row, so that much at least is consistent.

The only thiing i can think of to do is to create an elaborate "figure out which schema applies and route accordingly pipeline component", but before I go down that road I wanted to see if maybe anyone had some thoughts on way to make it work with a single flat file schema (I tried to set the minOccurs property of the optional columns to 0, but that was no good).

Thanks in advance for any advice.

Is there anything that would identify the different types of records at the front of the record? If there was, you could use a tag identifier. — aceinthehole, Jun 01 '11 at 20:09

Filburt · Accepted Answer · 2011-06-03T17:36:21.060

One way would be to define a "outer" schema and import schemas for the different versions you need to support. The "outer" schema will offer a choice block containing references to your imported version schemas.

If you need to add the next version you just have to import a new schema and add it to the choice.

The hard part of course is how you can determine how to process different versions. Maybe the simplest way would be to create a map for every dedicated type you need to handle. On the other hand you could extend a "latest-and-greatest" internal message type and decide based on message content.

"outer" schema

<!-- language: xml-schema -->

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:ns0="http://ACME.Version_001" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://ACME.Outer" xmlns:ns1="http://ACME.Version_002" targetNamespace="http://ACME.Outer" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:import schemaLocation=".\version_002.xsd" namespace="http://ACME.Version_002" />
    <xs:import schemaLocation=".\version_001.xsd" namespace="http://ACME.Version_001" />
    <xs:annotation>
        <xs:appinfo>
            <b:schemaInfo standard="Flat File" root_reference="Root" />
            <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
            <b:references>
                <b:reference targetNamespace="http://ACME.Version_001" />
                <b:reference targetNamespace="http://ACME.Version_002" />
            </b:references>
        </xs:appinfo>
    </xs:annotation>
    <xs:element name="Root">
        <xs:annotation>
            <xs:appinfo>
                <b:recordInfo structure="delimited" sequence_number="1" child_delimiter_type="hex" child_order="postfix" child_delimiter="0x0D 0x0A" />
            </xs:appinfo>
        </xs:annotation>
        <xs:complexType>
            <xs:sequence>
                <xs:choice minOccurs="1">
                    <xs:element ref="ns0:Version_001">
                        <xs:annotation>
                            <xs:appinfo>
                                <b:recordInfo sequence_number="1" structure="delimited" />
                            </xs:appinfo>
                        </xs:annotation>
                    </xs:element>
                    <xs:element ref="ns1:Version_002">
                        <xs:annotation>
                            <xs:appinfo>
                                <b:recordInfo sequence_number="2" structure="delimited" />
                            </xs:appinfo>
                        </xs:annotation>
                    </xs:element>
                </xs:choice>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

Imported schema "Version_001"

<!-- language: xml-schema -->

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://ACME.Version_001" targetNamespace="http://ACME.Version_001" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:annotation>
        <xs:appinfo>
            <b:schemaInfo standard="Flat File" root_reference="Version_001" />
            <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
        </xs:appinfo>
   </xs:annotation>
   <xs:element name="Version_001">
       <xs:annotation>
           <xs:appinfo>
               <b:recordInfo structure="delimited" child_delimiter_type="char" child_order="infix" rootTypeName="Version_001" child_delimiter="," />
           </xs:appinfo>
       </xs:annotation>
       <xs:complexType>
           <xs:sequence>
               <xs:element name="Col1" type="xs:string" />
               <xs:element name="Col2" type="xs:string" />
               <xs:element name="Col3" type="xs:string" />
           </xs:sequence>
       </xs:complexType>
   </xs:element>
</xs:schema>

Imported schema "Version_002"

<!-- language: xml-schema -->

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://ACME.Version_002" targetNamespace="http://ACME.Version_002" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:annotation>
        <xs:appinfo>
            <b:schemaInfo standard="Flat File" root_reference="Version_002" />
            <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
        </xs:appinfo>
   </xs:annotation>
   <xs:element name="Version_002">
       <xs:annotation>
           <xs:appinfo>
               <b:recordInfo structure="delimited" child_delimiter_type="char" child_order="infix" rootTypeName="Version_001" child_delimiter="," />
           </xs:appinfo>
       </xs:annotation>
       <xs:complexType>
           <xs:sequence>
               <xs:element name="Col1" type="xs:string" />
               <xs:element name="Col2" type="xs:string" />
               <xs:element name="Col3" type="xs:string" />
               <xs:element name="Col4" type="xs:string" />
           </xs:sequence>
       </xs:complexType>
   </xs:element>
</xs:schema>

(some default attributes omitted for readability)

Thanks Filburt, the "choice' schema sounds like the best solutiion. — Doug Griffin, Jun 07 '11 at 22:02

Biztalk schema variable number of columns in delimited file permitted?

1 Answers1