Query regarding flat file schema (Network Steve Forum)

Query regarding flat file schema

Hi,

I had a query regarding flat file positional schema, I have a input file

001ABC

010ABCDEFGHI

010ABCDEFGHIJKL

first record:record_001, has a tag identifier:001 and has only one element field of length 3

Second Record:record_010 has a tag identifier:010 and has two field elements of length 3 each

Third Record:Record_010_CD has a tag identifier:010 and has 4 field elements of length 3 each.

Note: All the records are repeating(Min occurs:0 and Max Occurs Unbounded)

Can we have same tag identifiers for second and third record?

Please suggest.

Thanks in advance.

Ranjana

Edited by Ranjana_Torreti 1 hour 22 minutes ago

August 19th, 2015 1:40pm

If you can live with the 2nd and 3rd record being the same XML Record, with the 3rd element being optional, you can do the following:

Here is some sample input (can't be sure this is the complete FF-structure, since you did not post a full message, or at least I assume you didn't):

001ABC
001ABC
010ABCDEFGHI
010ABCDEFGHIJKL
010ABCDEFGHI
010ABCDEFGHIJKL
010ABCDEFGHIJKL
010ABCDEFGHIJKL

Note: My FFSchema assumes that the document ends with a carriage return. Change Child Order from Postfix to Infix if this is not the case.

By using this FFSchema:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BizTalk_Server_Project2._001FF" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://BizTalk_Server_Project2._001FF" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <b:schemaInfo standard="Flat File" root_reference="MyRoot" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="true" 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="MyRoot">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" 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 minOccurs="0" maxOccurs="unbounded" name="Record001">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo sequence_number="1" structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="001" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <b:groupInfo sequence_number="0" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="Field1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="1" justification="left" pos_offset="3" pos_length="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Record010">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo sequence_number="2" structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="010" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <b:groupInfo sequence_number="0" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="Field1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="1" justification="left" pos_offset="3" pos_length="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Field2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="2" justification="left" pos_length="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element minOccurs="0" name="Field3" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo sequence_number="3" justification="left" pos_length="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

I get the following XML output:

<MyRoot xmlns="http://BizTalk_Server_Project2._001FF">
  <Record001 xmlns="">
    <Field1>ABC</Field1>
  </Record001>
  <Record001 xmlns="">
    <Field1>ABC</Field1>
  </Record001>
  <Record010 xmlns="">
    <Field1>ABC</Field1>
    <Field2>DEF</Field2>
    <Field3>GHI</Field3>
  </Record010>
  <Record010 xmlns="">
    <Field1>ABC</Field1>
    <Field2>DEF</Field2>
    <Field3>GHIJKL</Field3>
  </Record010>
  <Record010 xmlns="">
    <Field1>ABC</Field1>
    <Field2>DEF</Field2>
    <Field3>GHI</Field3>
  </Record010>
  <Record010 xmlns="">
    <Field1>ABC</Field1>
    <Field2>DEF</Field2>
    <Field3>GHIJKL</Field3>
  </Record010>
  <Record010 xmlns="">
    <Field1>ABC</Field1>
    <Field2>DEF</Field2>
    <Field3>GHIJKL</Field3>
  </Record010>
  <Record010 xmlns="">
    <Field1>ABC</Field1>
    <Field2>DEF</Field2>
    <Field3>GHIJKL</Field3>
  </Record010>
</MyRoot>

Morten la Cour

Free Windows Admin Tool Kit Click here and download it now

August 20th, 2015 1:06am

Hi Ranjana, La Cour,

Since you have updated fields count to 4 for 3rd row, i have updated some fields to the schema here.

- Add one more field to schema as shown below

- Update Min Occurs to 0 for the new field.

- Go Schema properties - Suppress Empty Nodes to Yes

- Validate instance and you should see the below output.

Snaps :

Thanks, SMSVikasK

August 20th, 2015 3:17am

Hi,

I tried generating the output you require (atleast the one which I understood from your query). The same has been suggested by La Cour and Vikas.

You can use the following Schema to get the desired response:

  <?xml version="1.0" encoding="utf16" ?> 
 <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://Test.FlatFileSch" targetNamespace="http://Test.FlatFileSch" 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="speed" lookahead_depth="3" suppress_empty_nodes="true" 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_delimiter_type="hex" child_order="infix" child_delimiter="0x0D 0x0A" /> 
  </xs:appinfo>
  </xs:annotation>
 <xs:complexType>
 <xs:sequence maxOccurs="unbounded">
 <xs:annotation>
 <xs:appinfo>
  <b:groupInfo sequence_number="0" /> 
  </xs:appinfo>
  </xs:annotation>
 <xs:element maxOccurs="unbounded" name="Record1">
 <xs:annotation>
 <xs:appinfo>
  <b:recordInfo sequence_number="1" structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="001" /> 
  </xs:appinfo>
  </xs:annotation>
 <xs:complexType>
 <xs:sequence>
 <xs:annotation>
 <xs:appinfo>
  <b:groupInfo sequence_number="0" /> 
  </xs:appinfo>
  </xs:annotation>
 <xs:element minOccurs="0" name="Element1" type="xs:string">
 <xs:annotation>
 <xs:appinfo>
  <b:fieldInfo sequence_number="1" justification="left" pos_length="3" pos_offset="3" /> 
  </xs:appinfo>
  </xs:annotation>
  </xs:element>
  </xs:sequence>
  </xs:complexType>
  </xs:element>
 <xs:element maxOccurs="unbounded" name="Record2">
 <xs:annotation>
 <xs:appinfo>
  <b:recordInfo sequence_number="2" structure="positional" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="010" /> 
  </xs:appinfo>
  </xs:annotation>
 <xs:complexType>
 <xs:sequence>
 <xs:annotation>
 <xs:appinfo>
  <b:groupInfo sequence_number="0" /> 
  </xs:appinfo>
  </xs:annotation>
 <xs:element minOccurs="0" name="Element2" type="xs:string">
 <xs:annotation>
 <xs:appinfo>
  <b:fieldInfo sequence_number="1" justification="left" pos_length="3" pos_offset="3" /> 
  </xs:appinfo>
  </xs:annotation>
  </xs:element>
 <xs:element minOccurs="0" name="Element3" type="xs:string">
 <xs:annotation>
 <xs:appinfo>
  <b:fieldInfo sequence_number="2" justification="left" pos_length="3" pos_offset="0" /> 
  </xs:appinfo>
  </xs:annotation>
  </xs:element>
 <xs:element minOccurs="0" name="Element4" type="xs:string">
 <xs:annotation>
 <xs:appinfo>
  <b:fieldInfo sequence_number="3" justification="left" pos_length="3" /> 
  </xs:appinfo>
  </xs:annotation>
  </xs:element>
 <xs:element minOccurs="0" name="Element5" type="xs:string">
 <xs:annotation>
 <xs:appinfo>
  <b:fieldInfo sequence_number="4" justification="left" pos_length="3" /> 
  </xs:appinfo>
  </xs:annotation>
  </xs:element>
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:schema>

Input Used:

001ABC
001ABC
010ABCDEF
010ABCDEFGHIJKL
010ABCDEFGHIJKL
010ABCDEFGHIJKL

Output:

- <Root xmlns="http://Test.FlatFileSch">
- <Record1 xmlns="">
  <Element1>ABC</Element1> 
  </Record1>
- <Record1 xmlns="">
  <Element1>ABC</Element1> 
  </Record1>
- <Record2 xmlns="">
  <Element2>ABC</Element2> 
  <Element3>DEF</Element3> 
  </Record2>
- <Record2 xmlns="">
  <Element2>ABC</Element2> 
  <Element3>DEF</Element3> 
  <Element4>GHI</Element4> 
  <Element5>JKL</Element5> 
  </Record2>
- <Record2 xmlns="">
  <Element2>ABC</Element2> 
  <Element3>DEF</Element3> 
  <Element4>GHI</Element4> 
  <Element5>JKL</Element5> 
  </Record2>
- <Record2 xmlns="">
  <Element2>ABC</Element2> 
  <Element3>DEF</Element3> 
  <Element4>GHI</Element4> 
  <Element5>JKL</Element5> 
  </Record2>
  </Root>

Please indicate "Mark as Answer" or "Mark as Helpful" if this post has answered the question

Rahul

Free Windows Admin Tool Kit Click here and download it now

August 20th, 2015 3:50am

This topic is archived. No further replies will be accepted.