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 Thursday, August 20, 2015 6:25 AM

August 19th, 2015 5:36pm

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

Marked as answer by Ranjana_Torreti 19 hours 32 minutes ago

Free Windows Admin Tool Kit Click here and download it now

August 20th, 2015 5:02am

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

Marked as answer by Ranjana_Torreti 19 hours 32 minutes ago

August 20th, 2015 7:13am

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

Marked as answer by Ranjana_Torreti 19 hours 32 minutes ago

Free Windows Admin Tool Kit Click here and download it now

August 20th, 2015 7:47am

Hi Ranjana,

From your query i observed, you need different schema structure for every row (001,010,010).

With the same Tag you cannot validate the schema, you will get error or warnings. (Same level Tag identifier should be unique to parse)

You have other workaround :

- Create pipeline component in decode stage, differentiate the rows with your logic, keep unique tag identifier, create schema for the updated message with unique Tags and then disassemble with default Flat file disassembler to get the desired output.

Similar issue already discussed here: you can replicate to your scenario, if applicable.

Tag Identifier Issues

Thanks, SMSVikasK

Marked as answer by Ranjana_Torreti 20 hours 12 minutes ago

August 20th, 2015 8:52am

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