Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> BizTalk Server >> BizTalk R2 General

Mapping multiple input records to single output record

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>

<PickupAddress>PickupAddressData</PickupAddress>

<DropAddress>DropAddressData</DropAddress>

</Record>

<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

[1003 byte] By [JodyPetroni] at [2008-1-7]

«« Create Stored usernames and passwords
»» Converting XML to Excel using XSLT

# 1

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

Thanks,

Saravana Kumar

SaravanaKumar_1977 at 2007-10-2 >

top of Msdn Tech,BizTalk Server,BizTalk R2 General...

# 2

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.

JodyPetroni at 2007-10-2 >

# 3

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

thanks

JodyPetroni at 2007-10-2 >

# 4

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

SaravanaKumar_1977 at 2007-10-2 >

# 5

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.

Regards,

Leonid Ganeline

http://geekswithblogs.net/leonidganeline/

LeonidGaneline-MVP at 2007-10-2 >

# 6

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

SaravanaKumar_1977 at 2007-10-2 >

# 7

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

Regards,

Leonid Ganeline

http://geekswithblogs.net/leonidganeline/

LeonidGaneline-MVP at 2007-10-2 >

# 8

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,...

JodyPetroni at 2007-10-2 >

# 9

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"?>
<xs

chema 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>
<schemaEditorExtension

chemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns

chemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
<b

chemaInfo 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>
<xs

equence>
<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>
<xs

equence>
<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="xs

tring">
<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>
<xs

equence>
<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="xs

tring">
<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="xs

tring">
<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="xs

tring">
<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="xs

tring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs

equence>
</xs:complexType>
</xs:element>
</xs

equence>
</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>
<xs

equence>
<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="xs

tring">
<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>
<xs

tring">
<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="xs

tring">
<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="xs

tring">
<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="xs

tring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="4" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs

equence>
</xs:complexType>
</xs:element>
</xs

equence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs

equence>
</xs:complexType>
</xs:element>
</xs

chema>

Regards,

Leonid Ganeline

http://geekswithblogs.net/leonidganeline/

LeonidGaneline-MVP at 2007-10-2 >

# 10

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

JodyPetroni at 2007-10-2 >

# 11

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

JodyPetroni at 2007-10-2 >

# 12

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.

Regards,

Leonid Ganeline

http://geekswithblogs.net/leonidganeline/

LeonidGaneline-MVP at 2007-10-2 >

# 13

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

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

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"?>
<xschema 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>
<bchemaInfo 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" />
<schemaEditorExtensionchemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlnschemaEditorExtension="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>
<xsequence>
<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>
<xsequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="PONumber" type="xstring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="1" justification="left" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Field" type="xstring">
<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>
<xsequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="LocationCode" type="xstring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="1" justification="left" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</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>
<xsequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="PONumber" type="xstring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo justification="left" sequence_number="1" />
</xs:appinfo>
</xs:annotation>
</xs:element>
<xs:element name="Field" type="xstring">
<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>
<xsequence>
<xs:annotation>
<xs:appinfo>
<b:groupInfo sequence_number="0" />
</xs:appinfo>
</xs:annotation>
<xs:element name="LocationCode" type="xstring">
<xs:annotation>
<xs:appinfo>
<b:fieldInfo sequence_number="1" justification="left" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

JodyPetroni at 2007-10-2 >

# 14

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

JodyPetroni at 2007-10-2 >

BizTalk Server Hot Topic

BizTalk Server New Topic

BizTalk Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

it's Archive about 'Mapping multiple input records to single output record...'

Mapping multiple input records to single output record

BizTalk Server Hot Topic

BizTalk Server New Topic

BizTalk Server

Site Classified