none
delimited flat file with tag identifier not first field RRS feed

  • Question

  • Hello,

    I have a delimited file that contains tag identifiers for header and detail.  Would like to avoid a decode component that moves the tags to the front of the record.  Also the file needs to debatch into multiple files containing header, detail sets.

    date~header~data~data~data~data
    date~detail~data~data
    date~detail~data~data
    date~detail~data~data
    date~header~data~data~data~data
    date~detail~data~data
    date~detail~data~data
    date~detail~data~data

    I've tried

    Schema

    root - delim = hex, 0x0D 0x0A, postfix

    node - delim = char, ~, prefix

    field = date

    choice - max = unbounded

    header - tag = header

    fields...

    detail - tag = detail

    fields...

    Thursday, August 22, 2013 8:12 PM

Answers

  • Hi 123123123e,

    My earlier schema would not work for your case as boatseller had spotted that I was using “Date” as a static value. Now I have corrected my schema and it looks like the following:

    I have used an approach as mentioned by Johann Cooper. And the schema for your purpose is as follows:

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns="http://TryMSDNQuestions.FFTagInMiddle.FlatFileSchema2" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://TryMSDNQuestions.FFTagInMiddle.FlatFileSchema2" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:annotation>
        <xs:appinfo>
          <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
          <b:schemaInfo standard="Flat File" codepage="65001" 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="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="CompleteBatch" />
        </xs:appinfo>
      </xs:annotation>
      <xs:element name="CompleteBatch">
        <xs:annotation>
          <xs:appinfo>
            <b:recordInfo structure="delimited" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" child_order="infix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
          </xs:appinfo>
        </xs:annotation>
        <xs:complexType>
          <xs:sequence>
            <xs:annotation>
              <xs:appinfo>
                <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
              </xs:appinfo>
            </xs:annotation>
            <xs:element maxOccurs="unbounded" name="Record">
              <xs:annotation>
                <xs:appinfo>
                  <recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_delimiter_type="char" child_delimiter="~" child_order="infix" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:complexType>
                <xs:sequence>
                  <xs:annotation>
                    <xs:appinfo>
                      <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                    </xs:appinfo>
                  </xs:annotation>
                  <xs:element name="Date" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <fieldInfo justification="left" sequence_number="1" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="RecordBatch">
                    <xs:annotation>
                      <xs:appinfo>
                        <recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" child_order="infix" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                      </xs:appinfo>
                    </xs:annotation>
                    <xs:complexType>
                      <xs:choice minOccurs="0">
                        <xs:annotation>
                          <xs:appinfo>
                            <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                          </xs:appinfo>
                        </xs:annotation>
                        <xs:element name="Header">
                          <xs:annotation>
                            <xs:appinfo>
                              <b:recordInfo tag_name="header" structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="prefix" 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>
                                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                                </xs:appinfo>
                              </xs:annotation>
                              <xs:element name="Header_Child1" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="1" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Header_Child2" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="2" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Header_Child3" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="3" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Header_Child4" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="4" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                        <xs:element name="Detail">
                          <xs:annotation>
                            <xs:appinfo>
                              <b:recordInfo tag_name="detail" structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="prefix" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" />
                            </xs:appinfo>
                          </xs:annotation>
                          <xs:complexType>
                            <xs:sequence>
                              <xs:annotation>
                                <xs:appinfo>
                                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                                </xs:appinfo>
                              </xs:annotation>
                              <xs:element name="Detail_Child1" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="1" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Detail_Child2" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="2" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                      </xs:choice>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>

    With this schema, if you use the sample input instance as for example:

    date~header~data~data~data~data
    date~detail~data~data
    date~detail~data~data
    date~detail~data~data
    date~header~data~data~data~data
    date~detail~data~data
    date~detail~data~data
    date~detail~data~data

    Then the output XML file would look like this..

    As you can see, you will have multiple “Record” corresponds to each row in the input flat file instance. But with same “date” repeated. Then I would map this flatfile schema to another schema for grouping the batches based on Date (I hope, you want to debatch the message by date. And the batch would contain the batch and detail record of your input flat file. Correct me if your debatching approach is different to my assumption)

    In the map you can apply grouping using Muenchian way. Use the following as reference

    Muenchian Grouping and Sorting in BizTalk Maps

    http://blogs.msdn.com/b/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx

    Muenchian Grouping and Sorting in BizTalk Maps without losing Map functionality

    http://code.msdn.microsoft.com/windowsdesktop/Muenchian-Grouping-and-790347d2


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.




    Tuesday, August 27, 2013 4:19 PM
  • Updating this post, as this could be useful to someone in future ..

    I have created a map as I described in my above comment using Muenchian. Following is the XSLT for the schema I have generated for you (the one exist in my above comment)

    <?xml version="1.0" encoding="UTF-16"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://TryMSDNQuestions.FFTagInMiddle.FlatFileSchema2">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />  
      
      <xsl:key use="Date" match="/ns0:CompleteBatch/Record" name ="groups"/>
      <xsl:template match="/">
        <xsl:apply-templates select="/ns0:CompleteBatch" />
      </xsl:template>
      <xsl:template match="/ns0:CompleteBatch">
        <ns0:CompleteBatch>
          <xsl:for-each select="Record[generate-id(.)=generate-id(key('groups',Date))]">        
              <Record>
              <Date>
                <xsl:value-of select="Date/text()" />
              </Date>
              <RecordBatch>            
                <xsl:for-each select ="key('groups',Date)">
                  <xsl:for-each select="RecordBatch/Header">
                  <Header>
                    <Header_Child1>
                      <xsl:value-of select="Header_Child1/text()" />
                    </Header_Child1>
                    <Header_Child2>
                      <xsl:value-of select="Header_Child2/text()" />
                    </Header_Child2>
                    <Header_Child3>
                      <xsl:value-of select="Header_Child3/text()" />
                    </Header_Child3>
                    <Header_Child4>
                      <xsl:value-of select="Header_Child4/text()" />
                    </Header_Child4>
                  </Header>
                </xsl:for-each>
                <xsl:for-each select="RecordBatch/Detail">
                <!--<xsl:for-each select ="key('groups',Date)">-->
                  <Detail>
                    <Detail_Child1>
                      <xsl:value-of select="Detail_Child1/text()" />
                    </Detail_Child1>
                    <Detail_Child2>
                      <xsl:value-of select="Detail_Child2/text()" />
                    </Detail_Child2>
                  </Detail>
                </xsl:for-each>
                </xsl:for-each>
              </RecordBatch>
            </Record>
          </xsl:for-each>
        </ns0:CompleteBatch>
      </xsl:template>
    </xsl:stylesheet>

    This XSLT is applied on the map where both the source and destination schemas are same (one I generated for you). If you use XSLT based map with this XSLT code in it, this would group the records based on Date field for batch. So grouped output would look like this (with some data to differentiate header and details)

    Now its standard way to debatch by <Record> node.


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.




    Tuesday, September 10, 2013 3:59 PM

All replies

  • The Tag property is only effective when the the Tag is the first field in the record.

    However, you probably can get it to work by looking at the file a little differently.  View it as if "date~" is it's own record, then "header~" or "detail~" become the start of the following record and the tag is the first fields.

    This means that the date won't be part of the header of detail record, but still workable in a Map.

    Basically, define the date as a single field postfix delimited record by the ~.


    Thursday, August 22, 2013 8:39 PM
    Moderator
  • Boatseller is onto it. I wrote a blog post on this topic which might help you too - http://adventuresinsidethemessagebox.wordpress.com/2013/05/25/creating-well-typed-biztalk-flat-file-schemas-which-contain-tags-in-the-middle-of-a-record/
    Friday, August 23, 2013 7:30 AM
  • Hi,

    Try this schema, which I created for your sample flat file you have provided. This schema would work for you. Change the namespace according to your need.

    Schema looks like this. Here you have to consider that your record have two tags “date~” and “Header” for Header record and “date~” and “detail” for Detail records.

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns="http://TryMSDNQuestions.FaltFileTag.FlatFileSchema3" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://TryMSDNQuestions.FaltFileTag.FlatFileSchema3" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:annotation>
        <xs:appinfo>
          <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
          <b:schemaInfo standard="Flat File" codepage="65001" 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="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="Root" />
        </xs:appinfo>
      </xs:annotation>
      <xs:element name="Root">
        <xs:annotation>
          <xs:appinfo>
            <b:recordInfo tag_name="date~" structure="delimited" child_delimiter_type="hex" child_delimiter="0xD 0xA" child_order="infix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
          </xs:appinfo>
        </xs:annotation>
        <xs:complexType>
          <xs:sequence>
            <xs:annotation>
              <xs:appinfo>
                <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
              </xs:appinfo>
            </xs:annotation>
            <xs:element name="HeaderRecord">
              <xs:annotation>
                <xs:appinfo>
                  <b:recordInfo tag_name="header~" structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="infix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
                </xs:appinfo>
              </xs:annotation>
              <xs:complexType>
                <xs:sequence>
                  <xs:annotation>
                    <xs:appinfo>
                      <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                    </xs:appinfo>
                  </xs:annotation>
                  <xs:element name="HDR_Data1" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="1" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="HDR_Data2" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="2" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="HDR_Data3" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="3" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="HDR_Data4" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="4" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element maxOccurs="unbounded" name="Detail">
              <xs:annotation>
                <xs:appinfo>
                  <b:recordInfo tag_name="date~" structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="infix" sequence_number="2" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
                </xs:appinfo>
              </xs:annotation>
              <xs:complexType>
                <xs:sequence>
                  <xs:annotation>
                    <xs:appinfo>
                      <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                    </xs:appinfo>
                  </xs:annotation>
                  <xs:element name="DTL_Data1" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="1" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="DTL_Data2" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="2" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="DTL_Data3" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <b:fieldInfo justification="left" sequence_number="3" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>

    Regards,

    M.R.ASHWINPRABHU


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.

    Friday, August 23, 2013 9:04 AM
  • Sorry, I don't think that will work.  It's highly unlikely the rows begin with the literal string "date~" so the OP can't use that string as the Tag.

    "date~" will likely be an actual value, "20130823" for example.

    Friday, August 23, 2013 12:43 PM
    Moderator
  • Thanks for your quick reply last night.  I've been attempting it now in multiple directions:)  
    Friday, August 23, 2013 5:51 PM
  • Hello,

    Thank you for your help.  The choice method does help create the correct records.  Though I still can't get it to debatch into multiple messages.  I'm also getting an error with the postfix delimiter at the end of the file.  Debatching is the real clincher currently.

    The advanced group max, min and order type on the record node is real dark magic and I'd like to better understand how it influences the interpreter.  Compared with using a Choice node there seems to be little difference except for the node nesting.  Dumping and comparing the two schema shows that the grouping on record creates more intense language.

    <?xml version="1.0" encoding="utf-16"?><xs:schema xmlns="http://donkey.FlatFileSchema1" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://donkey.FlatFileSchema1" xmlns:xs="http://www.w3.org/2001/XMLSchema">  <xs:annotation>    <xs:appinfo>      <b:schemaInfo standard="Flat File" 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="false" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="root" child_delimiter_type="char" default_child_order="postfix" default_child_delimiter="~" />      <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 sequence_number="1" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="true" />      </xs:appinfo>    </xs:annotation>    <xs:complexType>      <xs:sequence>        <xs:annotation>          <xs:appinfo>            <b:groupInfo sequence_number="0" />          </xs:appinfo>        </xs:annotation>        <xs:element name="preamble">          <xs:annotation>            <xs:appinfo>              <b:recordInfo sequence_number="1" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_delimiter_type="char" child_delimiter="~" child_order="postfix" />            </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 name="Field" type="xs:string">                <xs:annotation>                  <xs:appinfo>                    <b:fieldInfo sequence_number="1" justification="left" />                  </xs:appinfo>                </xs:annotation>              </xs:element>              <xs:element name="recordtype">                <xs:annotation>                  <xs:appinfo>                    <b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="infix" />                  </xs:appinfo>                </xs:annotation>                <xs:complexType>                  <xs:choice>                    <xs:annotation>                      <xs:appinfo>                        <b:groupInfo sequence_number="0" />                      </xs:appinfo>                    </xs:annotation>                    <xs:element maxOccurs="1" name="header">                      <xs:annotation>                        <xs:appinfo>                          <b:recordInfo sequence_number="1" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_delimiter_type="char" child_delimiter="~" child_order="prefix" tag_name="header" />                        </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:element name="Field" type="xs:string">                            <xs:annotation>                              <xs:appinfo>                                <b:fieldInfo justification="left" sequence_number="2" />                              </xs:appinfo>                            </xs:annotation>                          </xs:element>                          <xs:element name="Field" type="xs:string">                            <xs:annotation>                              <xs:appinfo>                                <b:fieldInfo justification="left" sequence_number="3" />                              </xs:appinfo>                            </xs:annotation>                          </xs:element>                          <xs:element name="Field" type="xs:string">                            <xs:annotation>                              <xs:appinfo>                                <b:fieldInfo justification="left" sequence_number="4" />                              </xs:appinfo>                            </xs:annotation>                          </xs:element>                        </xs:sequence>                      </xs:complexType>                    </xs:element>                    <xs:element maxOccurs="unbounded" name="detail">                      <xs:annotation>                        <xs:appinfo>                          <b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="detail" child_delimiter_type="char" child_delimiter="~" child_order="prefix" />                        </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:element name="Field" type="xs:string">                            <xs:annotation>                              <xs:appinfo>                                <b:fieldInfo justification="left" sequence_number="2" />                              </xs:appinfo>                            </xs:annotation>                          </xs:element>                        </xs:sequence>                      </xs:complexType>                    </xs:element>                  </xs:choice>                </xs:complexType>              </xs:element>            </xs:sequence>          </xs:complexType>        </xs:element>      </xs:sequence>    </xs:complexType>  </xs:element></xs:schema>


    Friday, August 23, 2013 9:57 PM
  • I've never noticed that the Group Min/Max have any affect.

    Sometimes it's easier to work backwards.  Meaning, instead of parsing a ff instance, generate an instance and modify until the output matches the real instance.

    Saturday, August 24, 2013 9:24 AM
    Moderator
  • Hello,  Replying to my own post:)

    So I was able to produce a well typed records but not the message structure that I wanted as follows.  The entire set of header, detail all fall into one message and the detail is not a child of header.  Not able to debatch via the schema. 

    What I wanted, header with child detail nodes.

      <header>
        <system>
          <date>date</date>
        </system>
        <data>data</data>
        <data>data</data>
        <data>data</data>
        <data>data</data>
        <detail>
          <system>
            <date>date</date>
          </system>
          <data>data</data>
          <data>data</data>
        </detail>
      </header>

    This is as far as I could get.  Notice how the header and detail are on the same level.

    <root>
      <header>
        <system>
          <date>date</date>
        </system>
        <data>data</data>
        <data>data</data>
        <data>data</data>
        <data>data</data>
      </header>
      <detail>
        <system>
          <date>date</date>
        </system>
        <data>data</data>
        <data>data</data>
      </detail>
    </root>

      I'll break up the file using a custom component on disassemble as others have done.   At the least if the structure was nested I'd be able to use the xmldisassembler technique to split up the message with an envelope schema.


    • Edited by 123123123e Monday, August 26, 2013 5:32 PM doh
    Monday, August 26, 2013 5:22 PM
  • Hi,

    You can go through following link :

    http://masteringbiztalkserver.wordpress.com/2011/09/22/how-to-debatch-split-a-flat-file-using-flat-file-schema/



    I hope this helps!!!!!!

    Maheshkumar S. Tiwari|BizTalk Developer Interview Questions and Answers

    http://tech-findings.blogspot.com/


    Monday, August 26, 2013 5:34 PM
  • You should be able to get the desired result by adding one more level between <detail> and <header>,
    Tuesday, August 27, 2013 1:16 PM
    Moderator
  • Hi boatSeller,

    Yes, you're correct. my above schema would not work as I used “date” as static value not as dynamic date value. Cheers for spotting it.


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.

    Tuesday, August 27, 2013 4:00 PM
  • Hi 123123123e,

    My earlier schema would not work for your case as boatseller had spotted that I was using “Date” as a static value. Now I have corrected my schema and it looks like the following:

    I have used an approach as mentioned by Johann Cooper. And the schema for your purpose is as follows:

    <?xml version="1.0" encoding="utf-16"?>
    <xs:schema xmlns="http://TryMSDNQuestions.FFTagInMiddle.FlatFileSchema2" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://TryMSDNQuestions.FFTagInMiddle.FlatFileSchema2" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:annotation>
        <xs:appinfo>
          <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
          <b:schemaInfo standard="Flat File" codepage="65001" 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="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="CompleteBatch" />
        </xs:appinfo>
      </xs:annotation>
      <xs:element name="CompleteBatch">
        <xs:annotation>
          <xs:appinfo>
            <b:recordInfo structure="delimited" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" child_order="infix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
          </xs:appinfo>
        </xs:annotation>
        <xs:complexType>
          <xs:sequence>
            <xs:annotation>
              <xs:appinfo>
                <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
              </xs:appinfo>
            </xs:annotation>
            <xs:element maxOccurs="unbounded" name="Record">
              <xs:annotation>
                <xs:appinfo>
                  <recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_delimiter_type="char" child_delimiter="~" child_order="infix" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:complexType>
                <xs:sequence>
                  <xs:annotation>
                    <xs:appinfo>
                      <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                    </xs:appinfo>
                  </xs:annotation>
                  <xs:element name="Date" type="xs:string">
                    <xs:annotation>
                      <xs:appinfo>
                        <fieldInfo justification="left" sequence_number="1" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                      </xs:appinfo>
                    </xs:annotation>
                  </xs:element>
                  <xs:element name="RecordBatch">
                    <xs:annotation>
                      <xs:appinfo>
                        <recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" child_order="infix" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                      </xs:appinfo>
                    </xs:annotation>
                    <xs:complexType>
                      <xs:choice minOccurs="0">
                        <xs:annotation>
                          <xs:appinfo>
                            <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                          </xs:appinfo>
                        </xs:annotation>
                        <xs:element name="Header">
                          <xs:annotation>
                            <xs:appinfo>
                              <b:recordInfo tag_name="header" structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="prefix" 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>
                                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                                </xs:appinfo>
                              </xs:annotation>
                              <xs:element name="Header_Child1" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="1" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Header_Child2" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="2" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Header_Child3" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="3" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Header_Child4" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="4" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                        <xs:element name="Detail">
                          <xs:annotation>
                            <xs:appinfo>
                              <b:recordInfo tag_name="detail" structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="prefix" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" />
                            </xs:appinfo>
                          </xs:annotation>
                          <xs:complexType>
                            <xs:sequence>
                              <xs:annotation>
                                <xs:appinfo>
                                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                                </xs:appinfo>
                              </xs:annotation>
                              <xs:element name="Detail_Child1" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="1" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                              <xs:element name="Detail_Child2" type="xs:string">
                                <xs:annotation>
                                  <xs:appinfo>
                                    <b:fieldInfo justification="left" sequence_number="2" />
                                  </xs:appinfo>
                                </xs:annotation>
                              </xs:element>
                            </xs:sequence>
                          </xs:complexType>
                        </xs:element>
                      </xs:choice>
                    </xs:complexType>
                  </xs:element>
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>

    With this schema, if you use the sample input instance as for example:

    date~header~data~data~data~data
    date~detail~data~data
    date~detail~data~data
    date~detail~data~data
    date~header~data~data~data~data
    date~detail~data~data
    date~detail~data~data
    date~detail~data~data

    Then the output XML file would look like this..

    As you can see, you will have multiple “Record” corresponds to each row in the input flat file instance. But with same “date” repeated. Then I would map this flatfile schema to another schema for grouping the batches based on Date (I hope, you want to debatch the message by date. And the batch would contain the batch and detail record of your input flat file. Correct me if your debatching approach is different to my assumption)

    In the map you can apply grouping using Muenchian way. Use the following as reference

    Muenchian Grouping and Sorting in BizTalk Maps

    http://blogs.msdn.com/b/chrisromp/archive/2008/07/31/muenchian-grouping-and-sorting-in-biztalk-maps.aspx

    Muenchian Grouping and Sorting in BizTalk Maps without losing Map functionality

    http://code.msdn.microsoft.com/windowsdesktop/Muenchian-Grouping-and-790347d2


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.




    Tuesday, August 27, 2013 4:19 PM
  • Use following schema as the output schema to group the input by date field.

    Obviously you have find a better solution than this, but with the available time I can come up with the one like this.


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.


    Tuesday, August 27, 2013 4:21 PM
  • This answer is just a concrete example of the the schema I detail in the original question and other detail in subsequent posts.  Who answers these questions:)   I like the clarity of your response with the images and code samples.  Thanks.

    Yes the solution doesn't exist to debatch direct from the schema for this type of file layout.  Running the result of this schema through another transform is a solution.  The other is to disassemble the inbound file into discrete messages then send those messages back through this kind of schema.  Either way there is more overhead to debatching the messages.

    My takeaway is that there are limitations to debatching delimited flat files.   Beware:)

    Thursday, August 29, 2013 4:32 PM
  • Updating this post, as this could be useful to someone in future ..

    I have created a map as I described in my above comment using Muenchian. Following is the XSLT for the schema I have generated for you (the one exist in my above comment)

    <?xml version="1.0" encoding="UTF-16"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://TryMSDNQuestions.FFTagInMiddle.FlatFileSchema2">
      <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />  
      
      <xsl:key use="Date" match="/ns0:CompleteBatch/Record" name ="groups"/>
      <xsl:template match="/">
        <xsl:apply-templates select="/ns0:CompleteBatch" />
      </xsl:template>
      <xsl:template match="/ns0:CompleteBatch">
        <ns0:CompleteBatch>
          <xsl:for-each select="Record[generate-id(.)=generate-id(key('groups',Date))]">        
              <Record>
              <Date>
                <xsl:value-of select="Date/text()" />
              </Date>
              <RecordBatch>            
                <xsl:for-each select ="key('groups',Date)">
                  <xsl:for-each select="RecordBatch/Header">
                  <Header>
                    <Header_Child1>
                      <xsl:value-of select="Header_Child1/text()" />
                    </Header_Child1>
                    <Header_Child2>
                      <xsl:value-of select="Header_Child2/text()" />
                    </Header_Child2>
                    <Header_Child3>
                      <xsl:value-of select="Header_Child3/text()" />
                    </Header_Child3>
                    <Header_Child4>
                      <xsl:value-of select="Header_Child4/text()" />
                    </Header_Child4>
                  </Header>
                </xsl:for-each>
                <xsl:for-each select="RecordBatch/Detail">
                <!--<xsl:for-each select ="key('groups',Date)">-->
                  <Detail>
                    <Detail_Child1>
                      <xsl:value-of select="Detail_Child1/text()" />
                    </Detail_Child1>
                    <Detail_Child2>
                      <xsl:value-of select="Detail_Child2/text()" />
                    </Detail_Child2>
                  </Detail>
                </xsl:for-each>
                </xsl:for-each>
              </RecordBatch>
            </Record>
          </xsl:for-each>
        </ns0:CompleteBatch>
      </xsl:template>
    </xsl:stylesheet>

    This XSLT is applied on the map where both the source and destination schemas are same (one I generated for you). If you use XSLT based map with this XSLT code in it, this would group the records based on Date field for batch. So grouped output would look like this (with some data to differentiate header and details)

    Now its standard way to debatch by <Record> node.


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.




    Tuesday, September 10, 2013 3:59 PM
  • Hi All,

    Posted a TechNet-Wiki article, detailing the solution for this question. Article also accompanies the complete source code this question.

    http://social.technet.microsoft.com/wiki/contents/articles/19644.biztalk-generating-flat-file-schema-for-file-with-tag-identifiers-not-at-the-beginning-grouping-data-by-element-and-debatching.aspx


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful.


    Wednesday, September 18, 2013 3:18 PM