CODEWITHSUNDEEP
biztalkbiztalk-2010
Andy
Andy

Reputation: 2318

How do you use BizTalk to create a flatfile with headers and lines from a query?

I am new to BizTalk and I am managing to create normal interfaces from Oracle to a flatfile.

But what most of our interfaces will be like is a combination of a header with lines of different length.

For example, the query would be ...

select oh.id, oh.name, ol.lineNo, ol.qty_ordered, ol.qty_shipped, ol.price 
from header oh, line ol

This would result in a flatfile similar to this ...

oh.id       |  oh.name
ol.lineNo   |  ol.qty_ordered  |  ol.qty_shipped  |  ol.price
ol.lineNo   |  ol.qty_ordered  |  ol.qty_shipped  |  ol.price
ol.lineNo   |  ol.qty_ordered  |  ol.qty_shipped  |  ol.price
oh.id       |  oh.name
ol.lineNo   |  ol.qty_ordered  |  ol.qty_shipped  |  ol.price
ol.lineNo   |  ol.qty_ordered  |  ol.qty_shipped  |  ol.price

I am sure this isn't a weird setup for BizTalk, but I have no clue how to call this so I can't really google it. So far I seem to have a problem creating a schema and mapping that will allow for the lines to be this different

Upvotes: 0

Views: 1771

Answers (2)

Chris
Chris

Reputation: 2481

If I'm understanding you correctly (I'm assuming you need a schema for mapper use), you need a schema that is something like 

     <?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://BizTalk_Server_Project1.FlatFileSchema1" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://BizTalk_Server_Project1.FlatFileSchema1" 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="Sales" wrap_char_type="char" default_wrap_char="&quot;" />
    </xs:appinfo>
  </xs:annotation>
<xs:element name="orders">
        <xs:complexType>
        <xs:sequence>
        <xs:element maxOccurs="unbounded" name="order">
        <xs:complexType>
        <xs:sequence>
        <xs:element maxOccurs="1" minOccurs="1" name="orderheader">
        <xs:complexType>
        <xs:sequence>
        <xs:element maxOccurs="1" minOccurs="1" name="ohID"/>
        <xs:element maxOccurs="1" minOccurs="1" name="ohName"/>
        </xs:sequence>
        </xs:complexType>
    </xs:element>
        <xs:element maxOccurs="1" minOccurs="1" name="orders">
        <xs:complexType>
        <xs:sequence>
        <xs:element maxOccurs="1" minOccurs="1" name="ohLineNo"/>
        <xs:element maxOccurs="1" minOccurs="1" name="ohQuantityOrdered"/>
        <xs:element maxOccurs="1" minOccurs="1" name="ohQuantityShipped"/>
        <xs:element maxOccurs="1" minOccurs="1" name="ohPrice"/>
        </xs:sequence>
        </xs:complexType>
    </xs:element>
        </xs:sequence>
        </xs:complexType>
        </xs:element>
        </xs:sequence>
        </xs:complexType>
        </xs:element></xs:schema>

Then using the mapper build as you need from your result?

Upvotes: 1

SteveC
SteveC

Reputation: 16793

I'd suggest using the BizTalk Schema Wizard msdn

Note: I've only done a little bit of work with BizTalk and flat files, but I found the article at codeproject useful as a starter

Upvotes: 1

Related Questions