BizTalk - Build XSD for flat file with positional delimiter-CodePudding

I am trying to build XSD file for converting a flat file (which does not have any delimiters) to xml file.

Below is the sample data:

0000000020220709200012000000  000000000  000000000  000000000  000000000             
002029832022070922162607090111057992132                                 F        
00210018202207091911210709033S061991911DS0649919111S067891911           F   
9999999900000000000020000000  000000000  000000000  000000000  000000000

Column	Start Position	End Position
Area	0	8
Date	9	16
Number of records in the line (this determines how many Change values are present in this line)	27	28
Change 1	31	25
Change 2 ( 11 positions from above)	42	46
Change 3 ( 11 positions from above)	53	57

In the above sample data, I want to eliminate first & last lines. Can anyone help me in sample XSD for the above data which gives me below output:

<Lookup_Value_Set>
     <Record>
          <Area>00202983</Area>
          <Date>20220709</Date>
          <Change>05799</Change>
     </Record>
     <Record>
          <Area>00210018</Area>
          <Date>20220709</Date>
          <Change>06199</Change>
     </Record>
     <Record>
          <Area>00210018</Area>
          <Date>20220709</Date>
          <Change>06499</Change>
     </Record>
</Lookup_Value_Set>

I have been stuck here for a week now :-(. Not able to determine what the delimiter for line should be & how I can skip first & last lines.

Additional context:

The file is from linux machine. So line breaks are LF & not CR LF
IMPORTANT: The file has additional spaces beyond the last character (total characters are 100 in a line but data is filled till position 73)
Using xmlns="http://schemas.microsoft.com/BizTalk/2003"
From what ever testing I did, I always end up with below error

Decoding of the flat file failed: 'Unexpected data found while looking for: '\n' The current definition being parsed is Lookup_Value_Set. The stream offset where the error occured is 75

Any help is greatly appreciated.

CodePudding user response：

With the below schema, it will parse the file and be close to what you want. It doesn't break the change2 etc. into it's own record, but you can do that in a map. I've not done change3, but you can add and amend that.

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://Scratch2.Schema.SO73481712" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://Scratch2.Schema.SO73481712" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo standard="Flat File" root_reference="Root" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="complexity" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" />
      <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="Root">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_order="postfix" child_delimiter_type="hex" child_delimiter="0x0A" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:annotation>
          <xs:appinfo>
            <b:groupInfo sequence_number="0" />
          </xs:appinfo>
        </xs:annotation>
        <xs:element name="Header">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <b:groupInfo sequence_number="0" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="Field" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="1" justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element maxOccurs="unbounded" name="Record">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" child_order="postfix" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <b:groupInfo sequence_number="0" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="Area" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" pos_length="8" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Date" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="2" justification="left" pos_length="8" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Changes" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" pos_length="1" pos_offset="10" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Change1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="4" pos_length="5" pos_offset="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element minOccurs="0" name="Change2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="5" pos_length="5" pos_offset="6" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Field" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="6" pos_length="26" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="F" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="7" pos_length="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="Hungry" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" pos_length="1" sequence_number="8" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="Footer">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo sequence_number="3" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="9" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <b:groupInfo sequence_number="0" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="Field" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="1" justification="left" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

You will get the below output

<Root xmlns="http://Scratch2.Schema.SO73481712">
    <Header xmlns="">
        <Field>0000000020220709200012000000  000000000  000000000  000000000  000000000</Field>
    </Header>
    <Record xmlns="">
        <Area>00202983</Area>
        <Date>20220709</Date>
        <Changes>0</Changes>
        <Change1>05799</Change1>
        <Change2/>
        <Field/>
        <F>F</F>
        <Hungry/>
        <Hungry/>
        <Hungry/>
        <Hungry/>
        <Hungry/>
        <Hungry/>
        <Hungry/>
        <Hungry/>
    </Record>
    <Record xmlns="">
        <Area>00210018</Area>
        <Date>20220709</Date>
        <Changes>0</Changes>
        <Change1>06199</Change1>
        <Change2>06499</Change2>
        <Field>19111S067891911</Field>
        <F>F</F>
        <Hungry/>
        <Hungry/>
        <Hungry/>
    </Record>
    <Footer xmlns="">
        <Field>999999900000000000020000000  000000000  000000000  000000000  000000000</Field>
    </Footer>
</Root>