CODEWITHSUNDEEP
xsdbiztalkflat-file
Vitaliy
Vitaliy

Reputation: 755

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

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

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

Upvotes: 1

Views: 3140

Answers (3)

Serkan Arslan
Serkan Arslan

Reputation: 13393

My solution is,

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

    enter image description here

  2. Create a destination schema for grouped result

    enter image description here

  3. Create a map for group your source schema.

enter image description here

  1. 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>
  1. 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/

Upvotes: 1

felixmondelo
felixmondelo

Reputation: 1474

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

Upvotes: 3

zurebe-pieter
zurebe-pieter

Reputation: 3266

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.

Upvotes: 1

Related Questions