Jody Petroni

Hi All,

I have a requirement in which I need to combine multiple
input records into a single output record.

Here is my sample input.
0001,PICK, PickupAddressData

0001,DROP,DropAddressData
0002,PICK,PickupAddressData

0002,DROP,DropAddressData


The expected Ouptut is,

<Root>

<Record>

<PONumber>0001</PONumber>

<PickupAddress>PickupAddressData</PickupAddress>

<DropAddress>DropAddressData</DropAddress>

</Record>

<Record>

<PONumber>0001</PONumber>

<PickupAddress>PickupAddressData</PickupAddress>

<DropAddress>DropAddressData</DropAddress>

</Record>

</Root>

Is this possible using biztalk mapping or do I have to use .net types (created from schemas of both doc types) etc to produce the output

Thanks




Re: BizTalk R2 General Mapping multiple input records to single output record

SaravanaKumar_1977

You no need to write .net code. Use Table mapping funtoid, it will solve your problem.

Thanks,

Saravana Kumar





Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Table Mapping works the opposite to what you suggest. That is it takes one record from the source and creates multiple on the destination. Thats what I thought anyways.

Ta






Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Just a further note, there is no such thing as a table mapping functiod only a table looping functiod

thanks






Re: BizTalk R2 General Mapping multiple input records to single output record

SaravanaKumar_1977

Yes you are correct. It is typo. I hope you have created the flat file schema which converts the flat file into xml file. The input and the desired out is not matching, could you please explain in detail

Thanks,

Saravana Kumar





Re: BizTalk R2 General Mapping multiple input records to single output record

Leonid Ganeline - MVP

My understanding is you don't need the mapping but only FlatFile schema, if this is possible to use the PICK etc. words as tags. Using FlatFile wizard it is easy to create the right schema.
Then FlatFile disassembler component takes care of conversion.





Re: BizTalk R2 General Mapping multiple input records to single output record

SaravanaKumar_1977

But his problem is not that much stright forward. He is trying to do some other things. Blindly if you see his input and output it is very stright forward map. That's why I requested him the input and expected output.

Thanks,

Saravana Kumar





Re: BizTalk R2 General Mapping multiple input records to single output record

Leonid Ganeline - MVP

Yeah, Saravana. You are right, absolutely. That's why I added "if this is possible to use the PICK etc. words as tags...".





Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Thanks for the replies,

Unfortunatley the PICK/DROP key words are not the first fields of the row. My understanding is that they have to identifiy the row!

eg my data will look like:

001,PICK,...

001,DROP...

002,PICK,...

002,DROP,...

not

PICK,001,...

DROP,001,...

PICK,002,...

DROP,002,...






Re: BizTalk R2 General Mapping multiple input records to single output record

Leonid Ganeline - MVP

Jody Petroni wrote:

Unfortunatley the PICK/DROP key words are not the first fields of the row. My understanding is that they have to identifiy the row!

Not at all.
I was able to create the schema wich could be verified your kind of data.(I don't test this schema on the different data, only on data below.)
I used slightly different data:
001,PICK,aaa,1,456,f
001,DO,aaa,1,456,f
002,PICK,aaa,1,456,f
002,DO,aaa,1,456,f
003,PICK,aaa,1,456,f
003,DO,aaa,1,456,f

The schema is:
< xml version="1.0" encoding="utf-16" >
<xsTongue Tiedchema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://Microsoft.Samples.BizTalk.WCF.WcfCustomAdapter.EchoService.BizTalkApp.FlatFileSchema5" targetNamespace="http://Microsoft.Samples.BizTalk.WCF.WcfCustomAdapter.EchoService.BizTalkApp.FlatFileSchema5" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<schemaEditorExtensionTongue TiedchemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlnsTongue TiedchemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
<bTongue TiedchemaInfo 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="postfix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:annotation>
<xs:appinfo>
<groupInfo sequence_number="1" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root_Child1">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="," child_order="infix" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root_Child2_Child1" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2">
<xs:annotation>
<xs:appinfo>
<b:recordInfo tag_name="PICK" structure="delimited" child_delimiter_type="char" child_delimiter="," child_order="prefix" sequence_number="2" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root_Child2_Child2_Child1" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2_Child2" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2_Child3" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2_Child4" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
<xs:element name="Root_Child2">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="," child_order="infix" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root_Child2_Child1" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2">
<xs:annotation>
<xs:appinfo>
<b:recordInfo tag_name="DO" structure="delimited" child_delimiter_type="char" child_delimiter="," child_order="prefix" sequence_number="2" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root_Child2_Child2_Child1" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2_Child2" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2_Child3" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="3" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Root_Child2_Child2_Child4" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xsTongue Tiedchema>





Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Thank you for your reply. The problem I have though is I need to group records together so the test data
001,PICK,aaa,1,456,f
001,DO,aaa,1,456,f
002,PICK,aaa,1,456,f
002,DO,aaa,1,456,f
003,PICK,aaa,1,456,f
003,DO,aaa,1,456,f

needs to be grouped by the first column
so this is what I want
<Root>
<record>
<PO>
<number>001</number>
<PICK>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</PICK>
<PICK>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</PICK>
</PO>
</record>
<record>
<PO>
<number>002</number>
<PICK>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</PICK>
<PICK>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</PICK>
</PO>
</record>
</Root>


I dont think I can do this with a schema/map combination and will have to create as .net class and create it manually!!

Jody





Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Sorry my last post was slightly wrong - Thank you for your reply. The problem I have though is I need to group records together so the test data
001,PICK,aaa,1,456,f
001,DO,aaa,1,456,f
002,PICK,aaa,1,456,f
002,DO,aaa,1,456,f
003,PICK,aaa,1,456,f
003,DO,aaa,1,456,f

needs to be grouped by the first column
so this is what I want
<Root>
<record>
<PO>
<number>001</number>
<PICK>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</PICK>
<DO>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</DO>
</PO>
</record>
<record>
<PO>
<number>002</number>
<PICK>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</PICK>
<DO>
<field1>aaa</field>
<field2>1</field2>
<field3>456</field3>
<field4>f</field4>
</DO>
</PO>
</record>
</Root>


I dont think I can do this with a schema/map combination and will have to create as .net class and create it manually!!

Jody





Re: BizTalk R2 General Mapping multiple input records to single output record

Leonid Ganeline - MVP

Jody,
You can sort the Xml with Xslt (map).
There are a lot of posts about sorting with Xslt in microsoft.public.xsl forum. and in the microsoft.public.biztalk.general. Sure you quickly find all you need.





Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Thanks again,

I am having trouble with the schema you sent me and adapting it to my situation...

I have modified your schema to reflect my business requriement. However It works as long as the data is a "PICK" record. When ever there is data with a tag of "DROP" Biztalk complains and saysit is expecting "PICK".

If my test data looks like:

PONumber,field,PICK,LocationCode

PONumber,field,PICK,LocationCode

its ok, however as soon as I add a DROP record I get the following error..

PONumber,field,PICK,LocationCode

PONumber,field,PICK,LocationCode

PONumber,field,DROP,LocationCode

PONumber,field,PICK,LocationCode

IGet the follwoing error:

Unexpected data found line 3 while looking for:

'PICK'

Whats going on here, any help is appreciated. Its worth noting that with out the field element it works in both scenarios!

Here is my schema:

< xml version="1.0" encoding="utf-16" >
<xsTongue Tiedchema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://Schemas.Woolworths.FlatFileSchema1" targetNamespace="http://Schemas.Woolworths.FlatFileSchema1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:appinfo>
<bTongue TiedchemaInfo 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="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" default_child_order="infix" child_delimiter_type="hex" default_child_delimiter="0x0D 0x0A" />
<schemaEditorExtensionTongue TiedchemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlnsTongue TiedchemaEditorExtension="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="default" child_order="postfix" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="1" />
</xs:appinfo>
</xs:annotation>
<xs:element minOccurs="0" name="DropRecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="infix" child_delimiter_type="char" child_delimiter="," sequence_number="1" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="PONumber" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="1" justification="left" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Field" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="DROP">
<xs:annotation>
<xs:appinfo>
<b:recordInfo sequence_number="3" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="DROP" child_order="prefix" child_delimiter_type="char" child_delimiter="," />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="LocationCode" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="1" justification="left" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="PickRecord">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_order="infix" child_delimiter_type="char" child_delimiter="," sequence_number="2" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="PONumber" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Field" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="2" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="PICK">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name="PICK" sequence_number="3" child_order="prefix" child_delimiter_type="char" child_delimiter="," />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xsTongue Tiedequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="LocationCode" type="xsTongue Tiedtring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="1" justification="left" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xsTongue Tiedequence>
</xs:complexType>
</xs:element>
</xsTongue Tiedchema>






Re: BizTalk R2 General Mapping multiple input records to single output record

Jody Petroni

Hi again,

Lookahead Depth seemed to be the culprit. was set to 3, changing it to 0 and every thing is ok.

what is the Lookahead Depth property used for

Jody