How to create flat file XSD-schema for two level hierarchical data?

Question

I am a newer in BizTalk and I must create an XSD-schema from a CSV-file:

As you can see, it includes header and orders. Every order has it's own order-header - row with name of the shop and filled Period field, but with empty Quantity, Price, Cost and Currency fields. Every order has few positions, which have filled all fields, except Period. Every order-header has ItemNumber = 0. How to create correct flat file schema in this situation?

OrderDate;OrderNumber;ItemNumber;DESCRIPTION_LONG;Quantity;Price;Cost;Period;Currency
30-04-17;9;0;Shop: McDonalds;;;;2017-04;
30-04-17;9;1;Double burger;2;5,99;11,98;;USD
30-04-17;9;2;Coca-Cola;2;2,19;4,38;;USD
30-04-17;10;0;Shop: Hunting and fishing;;;;2017-04;
30-04-17;10;1;Fishing rod;2;10,90;21,80;;USD
30-04-17;10;2;Bait;5;1,00;5,00;;USD
30-04-17;10;3;Hunting gun;1;999,00;999,00;;USD

Answer 1

You might be able to do this using tag Identifiers (see https://blogs.msdn.microsoft.com/biztalknotes/2013/02/05/flat-file-schema-creation-with-tag-identifiers-in-the-input-flat-file-repeating-in-a-random-fashion/ for an example), but I'm not 100% sure this would work without issues, since the identifier (ItemNumber = 0) is not in the beginning of the line. My experience shows me the Flat file disassembler probing has limited success in those cases.

My general recommendation to people in this case would be not to try and 'force' structure in your flat file schema, since obviously your flat file structure does not have it. What I see is that each line has the same structure/format. It's only functionally different.

From that perspective I would go for a Flat File schema matching your CSV-file structure (one type of record with comma-separated fields and with a newline as a line delimiter) and, from there, map to a schema which makes more sense functionally.

Answer 2

I think the best option is parse your Flat File to an XML without trying to 'force' the final structure, as Pieter said on before answer. Something like this:

<Root xmlns="http://BizTalkMassCopy.FlatFileSchema3">
  <Header xmlns="">
    <HeaderOrderDate>OrderDate</HeaderOrderDate>
    <HeaderOrderNumber>OrderNumber</HeaderOrderNumber>
    <HeaderItemNumber>ItemNumber</HeaderItemNumber>
    <HeaderDescription>DESCRIPTION_LONG</HeaderDescription>
    <HeaderQuantity>Quantity</HeaderQuantity>
    <HeaderPrice>Price</HeaderPrice>
    <HeaderCost>Cost</HeaderCost>
    <HeaderPeriod>Period</HeaderPeriod>
    <HeaderCurrency>Currency</HeaderCurrency>
  </Header>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>9</OrderNumber>
    <ItemNumber>0</ItemNumber>
    <Description>Shop: McDonalds</Description>
    <Quantity></Quantity>
    <Price></Price>
    <Cost></Cost>
    <Period>2017-04</Period>
    <Currency></Currency>
  </OrderItem>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>9</OrderNumber>
    <ItemNumber>1</ItemNumber>
    <Description>Double burger</Description>
    <Quantity>2</Quantity>
    <Price>5,99</Price>
    <Cost>11,98</Cost>
    <Period></Period>
    <Currency>USD</Currency>
  </OrderItem>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>9</OrderNumber>
    <ItemNumber>2</ItemNumber>
    <Description>Coca-Cola</Description>
    <Quantity>2</Quantity>
    <Price>2,19</Price>
    <Cost>4,38</Cost>
    <Period></Period>
    <Currency>USD</Currency>
  </OrderItem>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>10</OrderNumber>
    <ItemNumber>0</ItemNumber>
    <Description>Shop: Hunting and fishing</Description>
    <Quantity></Quantity>
    <Price></Price>
    <Cost></Cost>
    <Period>2017-04</Period>
    <Currency></Currency>
  </OrderItem>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>10</OrderNumber>
    <ItemNumber>1</ItemNumber>
    <Description>Fishing rod</Description>
    <Quantity>2</Quantity>
    <Price>10,90</Price>
    <Cost>21,80</Cost>
    <Period></Period>
    <Currency>USD</Currency>
  </OrderItem>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>10</OrderNumber>
    <ItemNumber>2</ItemNumber>
    <Description>Bait</Description>
    <Quantity>5</Quantity>
    <Price>1,00</Price>
    <Cost>5,00</Cost>
    <Period></Period>
    <Currency>USD</Currency>
  </OrderItem>
  <OrderItem xmlns="">
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>10</OrderNumber>
    <ItemNumber>3</ItemNumber>
    <Description>Hunting gun</Description>
    <Quantity>1</Quantity>
    <Price>999,00</Price>
    <Cost>999,00</Cost>
    <Period></Period>
    <Currency>USD</Currency>
  </OrderItem>
</Root>

With a flat schema similar to this:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://BizTalkMassCopy.FlatFileSchema3" targetNamespace="http://BizTalkMassCopy.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 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="Header">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo 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="HeaderOrderDate" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderOrderNumber" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderItemNumber" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderDescription" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="4" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderQuantity" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="5" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderPrice" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="6" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderCost" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="7" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderPeriod" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="8" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="HeaderCurrency" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="9" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element maxOccurs="unbounded" name="OrderItem">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo 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="OrderDate" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="OrderNumber" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ItemNumber" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Description" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="4" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Quantity" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="5" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Price" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="6" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Cost" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="7" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Period" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="8" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Currency" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <fieldInfo sequence_number="9" justification="left" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

And then use a map to obtain the final result, with grouping mechanism such as Grouping in Biztalk Map Based on Multiple Elements

Answer 3

My solution is,

1. Create a source schema for parse your flat file.

   

2. Create a destination schema for grouped result

   

3. Create a map for group your source schema.

4. Add this Inline XSLT Scripts to your map

For the first script functoid

<xsl:key name="groups" match="Order" use="OrderNumber"/>

For the second script functoid

<xsl:for-each select="Order[generate-id(.)=generate-id(key('groups',OrderNumber))]">
   <xsl:sort select="OrderNumber" order="ascending"/>
      <xsl:choose>
         <xsl:when test="OrderDate != 'OrderDate'">
            <Order>
               <OrderDate><xsl:value-of select="OrderDate/text()" /></OrderDate>
               <OrderNumber><xsl:value-of select="OrderNumber/text()" /></OrderNumber>
               <Description><xsl:value-of select="Description/text()" /></Description>
               <Period><xsl:value-of select="Period/text()" /></Period>
               <Items>
                  <xsl:for-each select="key('groups',OrderNumber)">
                     <xsl:choose>
                        <xsl:when test="ItemNumber > '0'">
                           <Item>
                              <ItemNumber><xsl:value-of select="ItemNumber" /></ItemNumber>
                              <Quantity><xsl:value-of select="Quantity" /></Quantity>
                              <Price><xsl:value-of select="Price" /></Price>
                              <Cost><xsl:value-of select="Cost" /></Cost>
                              <Currency><xsl:value-of select="Currency" /></Currency>
                           </Item>
                        </xsl:when>
                     </xsl:choose>
                  </xsl:for-each>
               </Items>
            </Order>
         </xsl:when>
      </xsl:choose>   
</xsl:for-each>

5. Test your map and get this output.

<ns0:Orders xmlns:ns0="http://BizTalkServerTestProject.FlatFileSchema6">
  <Order>
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>10</OrderNumber>
    <Description>Shop: Hunting and fishing</Description>
    <Period>2017-04</Period>
    <Items>
      <Item>
        <ItemNumber>1</ItemNumber>
        <Quantity>2</Quantity>
        <Price>10,90</Price>
        <Cost>21,80</Cost>
        <Currency>USD</Currency>
      </Item>
      <Item>
        <ItemNumber>2</ItemNumber>
        <Quantity>5</Quantity>
        <Price>1,00</Price>
        <Cost>5,00</Cost>
        <Currency>USD</Currency>
      </Item>
      <Item>
        <ItemNumber>3</ItemNumber>
        <Quantity>1</Quantity>
        <Price>999,00</Price>
        <Cost>999,00</Cost>
        <Currency>USD</Currency>
      </Item>
    </Items>
  </Order>
  <Order>
    <OrderDate>30-04-17</OrderDate>
    <OrderNumber>9</OrderNumber>
    <Description>Shop: McDonalds</Description>
    <Period>2017-04</Period>
    <Items>
      <Item>
        <ItemNumber>1</ItemNumber>
        <Quantity>2</Quantity>
        <Price>5,99</Price>
        <Cost>11,98</Cost>
        <Currency>USD</Currency>
      </Item>
      <Item>
        <ItemNumber>2</ItemNumber>
        <Quantity>2</Quantity>
        <Price>2,19</Price>
        <Cost>4,38</Cost>
        <Currency>USD</Currency>
      </Item>
    </Items>
  </Order>
</ns0:Orders>

You can find full source code of the schemas and the map from this URL.http://blog.sarslan.com/group-source-schema-in-a-map/