BizTalk 2004: Delimited flat file schema with multiple record types

Matthew Roche

unread,

May 30, 2004, 10:40:59 PM5/30/04

to

Greetings:

I have a delimited flat file being produced by a legacy system that
contains a variety of different record types. I need to create a flat file
schema so that I can map these records to SQL Server stored procedures using
the SQL Adapter and enter their data into a SQL Server database. I've been
through the BizTalk 2004 documentation and the public BizTalk Server
newsgroups and have not found any information that will help me.

Here is a sample extract from the files I need to process:

20040508,"000175",5
"000175",20040508,"XD9",4,"",$0.00,$0.00,""
"000175",20040508,"XM8",7,"STRING VAL","XYZ-AB",1,$3.99,$140.46
"000175",20040508,"XM9",8,"STRING",1,$2.99,$0.00
"000175",20040508,"XM8",9,"STRING VAL","XYZ-7",4,$3.69,$0.00

Here are some significant characteristics of the file format:

1) The file format uses CrLf as its record delimiter and comma as its
field delimiter.
2) The first record in the file includes the date in yyyyMMdd format, a
data source identifier (a numeric identifier that gives context to the data
in the file) and an integer listing the total number of records in the file.
3) Every other record begins with the data source identifier (the same
value as the second field in the first record) as the first field, followed
by the date (the same value as the first field in the first record).
4) The third field in all records after the first is a string that
identifies the record type. There are four record types listed in the sample
extract above; there are over 50 in the actual file format I need to handle.
5) Each record type has its own well-defined schema, with specific
fields being included for each record type.
6) Other than the first record, the records in the file can appear in
any order. There is nothing to say that an AA1 record would appear before a
ZZ9 record, for example.

The only thing in the BizTalk schema editor that I have found that looks
even remotely helpful is the Tag Identifier node property ("You can use the
Tag Identifier property to specify the tag within a delimited record" sounds
helpful, right?), the BizTalk Server documentation includes this text:
"Unlike tags in positional records, tags in delimited records must occur at
the beginning of the delimited record and are automatically never included
in the data when the record is translated to its equivalent XML format."
That sounds a lot LESS helpful. Because the record identifier in my files is
not the first field in the record, and because the data in the first two
fields will vary from file to file, it does not appear that I can use this
property without doing a lot of extra legwork.

The only approach that looks like it will solve my problem is to write a
custom pipeline component to be executed in the Decode pipeline stage, and
to have that custom component rewrite the incoming file stream to place the
record identifier at the beginning of each record, so that the flat file
disassembler will find this information in the location where it can process
it. With this approach, the file stream for the example above will look like
this when it gets processed by the flat file disassembler:

20040508,"000175",5
XD9,"000175",20040508,"XD9",4,"",$0.00,$0.00,""
XM8,"000175",20040508,"XM8",7,"STRING VAL","XYZ-AB",1,$3.99,$140.46
XM9,"000175",20040508,"XM9",8,"STRING",1,$2.99,$0.00
XM8,"000175",20040508,"XM8",9,"STRING VAL","XYZ-7",4,$3.69,$0.00

I'd like to avoid having to take this step, because I fear what it will
do to my schedule (I've never written a custom pipeline component before)
and my runtime performance, as I will eventually be having tens of thousands
of these files, each with hundreds of records, incoming each day. Is there
any simpler way to identify different record types in a delimited flat file
like this one?

Any help you can provide will be greatly appreciated. Even if you do not
have a complete answer, if you can point me in the right direction it would
be of great value. Thanks in advance!

Matthew

Matthew Roche

unread,

May 31, 2004, 10:31:03 AM5/31/04

to

I realize it's probably bad form to post follow-ups to one's own messages, but I've been doing some additional research in an attempt to avoid unpleasant surprises down the road. I still need to solve the problem listed in the first message in this thread, but I believe I've found another one waiting to be uncovered.

If I manually update my sample input document instance to include the Tag Identifier values for each row, I can get the instance to validate successfully, but only if the detail rows are in sequence (which they will not be in the real documents I need to process) or if there are only one of each record type. I have been experimenting - unsuccessfully - with the Group Order Type property of the document root node, which seems to be what will control this part of the document schema. This is what the BizTalk Server documentation says about this property:

Allowed Values:
All: Specifies the element group as an all group. All groups allow their child elements to appear zero (0) or one (1) time, and in any order, in instance messages. Restrictions apply; see remarks for more information.
Choice: Specifies the element group as a choice group. A choice group allows only one of its child elements to appear in instance messages.
Sequence: Specifies the element group as a sequence group. Sequence groups require that child elements in instance messages appear in the same order as defined in the schema. This is the default value.

None of these values appear to do what I need. Currently, using "Sequence" as the Group Order Type property value I can parse this document instance (where the detail records appear in the same order as they are defined in the schema):

HDR,20040508,"000175",5

XD9,"000175",20040508,"XD9",4,"",$0.00,$0.00,""
XM8,"000175",20040508,"XM8",7,"STRING VAL","XYZ-AB",1,$3.99,$140.46

XM8,"000175",20040508,"XM8",9,"STRING VAL","XYZ-7",4,$3.69,$0.00
XM9,"000175",20040508,"XM9",8,"STRING",1,$2.99,$0.00

but can not parse this one (where the detail records appear in the pseudo-random order in which they will appear in "live" documents):

HDR,20040508,"000175",5

XD9,"000175",20040508,"XD9",4,"",$0.00,$0.00,""
XM8,"000175",20040508,"XM8",7,"STRING VAL","XYZ-AB",1,$3.99,$140.46
XM9,"000175",20040508,"XM9",8,"STRING",1,$2.99,$0.00
XM8,"000175",20040508,"XM8",9,"STRING VAL","XYZ-7",4,$3.69,$0.00

I have been unable to find *any* information about parsing this type of flat file using BizTalk Server 2004, which has been very frustrating. I have, however, found EDI samples in the BizTalk SDK that appear to do what I need. The X124010850Schema.xsd EDI schema from the sample in %InstallFolder%\EDI\Adapter\Getting Started with EDI\Visual Studio Projects\Getting Started with EDI\Session 1 appears to do what I need, but I cannot see how it does it. This EDI schema has the Group Order Type value of "Sequence" but in the sample instance documents included with the EDI sample application the various child nodes (such as PER and PID) appear repeatedly.

Is there a way to support this type of flat file in BizTalk Server 2004?

----- Matthew Roche wrote: -----

David Downing [MSFT]

unread,

Jun 7, 2004, 12:53:17 PM6/7/04

to

Matthew,

You might try something like the following:

<Root> delimited <CR><LF> sequence
<HDR> delimited , sequence min/max=1
...
<choice> min=1 max=unbounded
<XD9> delimited , sequence
...
<XM8> delimited , sequence
...
<XM9> delimited , sequence

Structuring the schema in this way will allow the repeating choice group to
match any of the child records until one is not found. If you're still
having problems after trying the above, please attach your schema and test
instance data for us to take a look at.

--
Dave

This posting is provided "AS IS" with no warranties, and confers no rights.

Matthew Roche

unread,

Jun 9, 2004, 1:11:04 PM6/9/04

to

David:

Thank you *very* much for your reply. The inclusion of a <choice> group did the trick and I was able to successfully process an input instance with multiple detail record types in arbitrary orders.

Your reply did not, however, address my original posting - is it possible for you to respond to this as well? The test instance I have processed with your help is representative of what will potentially be created by the custom pipeline component I mentioned in my original post. I would hope to avoid creating this custom pipeline component if possible, or at least get validation that writing a custom pipeline component is what I need to do. Thanks in advance!

(I don't know if this matters or not, but I am an MSDN Universal subscriber using the MSDN Managed newsgroups. I registered to use the groups with the promised two-business-day turnaround but never received any instructions on how to identify myself using this registration when using the web-based news interface. What should I do to get the advertised response time?)

Matthew

----- "David Downing [MSFT]" wrote: -----

David Downing [MSFT]

unread,

Jun 9, 2004, 3:42:46 PM6/9/04

to

Sorry I missed the response to your original post...

I believe if you modify your schema as follows, you will be able to get
things working without the need for a custom component.

<Root> delimited <CR><LF> sequence
<HDR> delimited , sequence min/max=1
...

<EntryRecord> delimited , sequence
<000175_Field>
<20040508_Field>

<choice> min=1 max=unbounded
<XD9> delimited , sequence
...
<XM8> delimited , sequence
...
<XM9> delimited , sequence

It appears as though the first two fields of each record are the same, you
should be able to create a new record that contains these fields along with
the choice group to process the remaining part of each record.

I'm not sure about the MSDN Managed newsgroups question you posted. :-(

Let me know if you need clarification.

--
Dave

This posting is provided "AS IS" with no warranties, and confers no rights.

Matthew Roche

unread,

Jun 10, 2004, 11:16:03 AM6/10/04

to

David:

Thanks for your timely reply. I believe I have things set up as directed, but I am getting errors when attempting to validate a test instance. I've included the schema (as much as will fit), the instance and the error message below - do you have any idea what I've done wrong here?

The test instance:

20040508,"000175",5
"000175",20040508,"XD9",4,"FOO"

"000175",20040508,"XM8",7,"STRING VAL","XYZ-AB"

"000175",20040508,"XM9",8,"STRING",1,$2.99

"000175",20040508,"XM8",9,"STRING VAL","XYZ-7"

The Schema:

<?xml version="1.0" encoding="utf-16"?><xs:schema xmlns="http://BizTalk_Research.TACO_Mockup_Schema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://BizTalk_Research.TACO_Mockup_Schema" xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:annotation><xs:appinfo><b:schemaInfo count_positions_by_byte="false" standard="Flat File" root_reference="Root" child_delimiter_type="char" default_child_delimiter="," repeating_delimiter_type="hex" default_repeating_delimiter="0x0D 0x0A" wrap_char_type="char" default_wrap_char=""" /><schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="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="hex" repeating_delimiter_type="default" child_delimiter="0x0D 0x0A" /></xs:appinfo></xs:annotation><xs:complexType><xs:sequence><xs:annotation><xs:appinfo><b:groupInfo sequence_number="0" /></xs:appinfo></xs:annotation><xs:element minOccurs="1" maxOccurs="1" name="HDR"><xs:annotation><xs:appinfo><b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" repeating_delimiter_type="default" child_delimiter_type="default" /></xs:appinfo></xs:annotation><xs:complexType><xs:sequence minOccurs="1" maxOccurs="1"><xs:annotation><xs:appinfo><b:groupInfo sequence_number="0" /></xs:appinfo></xs:annotation><xs:element name="Date" type="xs:date"><xs:annotation><xs:appinfo><b:fieldInfo justification="left" sequence_number="1" datetime_format="yyyyMMdd" /></xs:appinfo></xs:annotation></xs:element><xs:element name="StoreNum" type="xs:integer"><xs:annotation><xs:appinfo><b:fieldInfo justification="left" sequence_number="2" wrap_char_type="default" /></xs:appinfo></xs:annotation></xs:element><xs:element name="RecordCount" type="xs:string"><xs:annotation><xs:appinfo><b:fieldInfo justification="left" sequence_number="3" /></xs:appinfo></xs:annotation></xs:element></xs:sequence></xs:complexType></xs:element><xs:element name="Record"><xs:annotation><xs:appinfo><b:recordInfo sequence_number="2" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" child_delimiter_type="default" repeating_delimiter_type="default" /></xs:appinfo></xs:annotation><xs:complexType><xs:sequence><xs:annotation><xs:appinfo><b:groupInfo sequence_number="0" /></xs:appinfo></xs:annotation><xs:element name="StoreNum" type="xs:string"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="1" justification="left" wrap_char_type="default" /></xs:appinfo></xs:annotation></xs:element><xs:element name="Date" type="xs:date"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="2" justification="left" datetime_format="yyyyMMdd" wrap_char_type="default" /></xs:appinfo></xs:annotation></xs:element><xs:choice minOccurs="1" maxOccurs="unbounded"><xs:annotation><xs:appinfo><b:groupInfo sequence_number="3" /></xs:appinfo></xs:annotation><xs:element name="XD9"><xs:annotation><xs:appinfo><b:recordInfo sequence_number="1" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name=""XD9"" child_delimiter_type="default" repeating_delimiter_type="default" /></xs:appinfo></xs:annotation><xs:complexType><xs:sequence><xs:annotation><xs:appinfo><b:groupInfo sequence_number="0" /></xs:appinfo></xs:annotation><xs:element name="Quantity" type="xs:integer"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="1" justification="left" /></xs:appinfo></xs:annotation></xs:element><xs:element name="Description" type="xs:string"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="2" justification="left" wrap_char_type="default" /></xs:appinfo></xs:annotation></xs:element></xs:sequence></xs:complexType></xs:element><xs:element name="XM8"><xs:annotation><xs:appinfo><b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="2" tag_name=""XM8"" child_delimiter_type="default" repeating_delimiter_type="default" /></xs:appinfo></xs:annotation><xs:complexType><xs:sequence><xs:annotation><xs:appinfo><b:groupInfo sequence_number="0" /></xs:appinfo></xs:annotation><xs:element name="Quantity" type="xs:integer"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="1" justification="left" /></xs:appinfo></xs:annotation></xs:element><xs:element name="Description1" type="xs:string"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="2" justification="left" wrap_char_type="default" /></xs:appinfo></xs:annotation></xs:element><xs:element name="Description2" type="xs:string"><xs:annotation><xs:appinfo><b:fieldInfo sequence_number="3" justification="left" wrap_char_type="default" /></xs:appinfo></xs:annotation></xs:element></xs:sequence></xs:complexType></xs:element><xs:element name="XM9"><xs:annotation><xs:appinfo><b:recordInfo sequence_number="3" structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" tag_name=""XM9"" child_delimiter_type="default" repeating_delimiter_type="default" /></xs:appinfo></xs:annotation>
...

The error:

C:\Projects\BizTalk_Research\TACO_Mockup_Input_Instance.txt: error BEC2004: Unexpected data found while looking for:
','
The current definition being parsed is Record. The stream offset where the error occured is 54.

If you need the complete schema just let me know. Thanks in advance!

Matthew

David Downing [MSFT]

unread,

Jun 10, 2004, 12:10:07 PM6/10/04

to

Matthew,

Can you attach your schema and test instance to a post?

Thank you,

Matthew Roche

unread,

Jun 10, 2004, 10:34:01 PM6/10/04

to

David:

The MSDN web UI does not allow attachments (or I'm overlooking it) and the reply I posted using my ISP account with attachments many hours ago has not appeared. If I can email the files to you please let me know how.

Matthew

David Downing [MSFT]

unread,

Jun 11, 2004, 2:24:18 PM6/11/04

to

Matthew,

I connect to the newsgroups using Outlook Express. There is a checkbox in
the Tools | Options | Security tab called:

Do not allow attachments to be saved or opened that could potentially be a
virus.

If you can connect to the newsgroup using Outlook Express, make sure this is
not checked.

--
Dave

This posting is provided "AS IS" with no warranties, and confers no rights.

"Matthew Roche" <Matthe...@discussions.microsoft.com> wrote in message
news:AAE2FE49-B976-4593...@microsoft.com...

Matthew Roche

unread,

Jun 11, 2004, 4:30:01 PM6/11/04

to

David:

To the best of my knowledge I am not having issues with Outlook Express other than the message I posted with the two file attachments not appearing in the newsgroups. (The setting you mention is not checked, regardless.) The original message in this thread was posted using Outlook Express and my ISP account (as opposed to using the MSDN web UI) and it appears in the group; the only difference I see is the presence of the attachments in the post I sent yesterday, which has not yet appeared. It is possible that my ISP is blocking attachments for this group as it is not a "binaries" newsgroup.

Is there another way for me to get these files to you? I can share them via FTP or HTTP, but I would prefer not to post the links over a public forum.

Matthew Roche

unread,

Jun 11, 2004, 5:48:01 PM6/11/04

to

David:

I've shared the schema and a test instance here:

ftp://biztalk.kicks-ass.net/bts/TACO_Mockup_Input_Instance.txt
ftp://biztalk.kicks-ass.net/bts/TACO_Mockup_Schema.xsd

Please let me know if there is anything else you need - thanks again for your assistance.

Matthew Roche

unread,

Jun 14, 2004, 7:46:01 AM6/14/04

to

David:

Thank you very much for your assistance. I was able to get your attachment without any issues. I can now start applying your help to the actual documents I'm receiving from the legacy system instead of working with these simplified sample instances.

I must admit that I would *never* have thought to use the <choice> element in the way you did, even with a lot of time spent poring through the BizTalk documentation. Can you recommend any books or other reading that might help me enhance my ability to create BizTalk schemas? Thanks in advance!

Matthew

"David Downing [MSFT]" wrote:

> Matthew,
>
> I hope you're able to get the attachements... if not here's a summary of my
> findings:
>
> I changed the choice group to have min/max of 1 (my previous post was
> slightly wrong... typing faster than I was thinking).
> I set the Record to Max Occurs of unbounded. With delimiter of ',' infix
> order.
> I set the XD9, XM8 and XM9 records to include a delimiter of ',' infix
> order. I added a ',' to the tag name.
>
> With these changes the following xml is produced.
>
> <Root xmlns="http://BizTalk_Research.TACO_Mockup_Schema">
> <HDR xmlns="">
> <Date>2004-05-08</Date>
> <StoreNum>000175</StoreNum>
> <RecordCount>5</RecordCount>
> </HDR>
> <Record xmlns="">
> <StoreNum>000175</StoreNum>
> <Date>2004-05-08</Date>
> <XD9>
> <Quantity>4</Quantity>
> <Description>FOO</Description>
> </XD9>
> </Record>
> <Record xmlns="">
> <StoreNum>000175</StoreNum>
> <Date>2004-05-08</Date>
> <XM8>
> <Quantity>7</Quantity>
> <Description1>STRING VAL</Description1>
> <Description2>XYZ-AB</Description2>
> </XM8>
> </Record>
> <Record xmlns="">
> <StoreNum>000175</StoreNum>
> <Date>2004-05-08</Date>
> <XM9>
> <Quantity>8</Quantity>
> <Description>STRING</Description>
> <ItemNum>1</ItemNum>
> <Price>$2.99</Price>
> </XM9>
> </Record>
> <Record xmlns="">
> <StoreNum>000175</StoreNum>
> <Date>2004-05-08</Date>
> <XM8>
> <Quantity>9</Quantity>
> <Description1>STRING VAL</Description1>
> <Description2>XYZ-7</Description2>
> </XM8>
> </Record>
> </Root>
>
> Here is the schema text for reference:

>
> <?xml version="1.0" encoding="utf-16"?>
> <xs:schema xmlns="http://BizTalk_Research.TACO_Mockup_Schema"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
> targetNamespace="http://BizTalk_Research.TACO_Mockup_Schema"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:appinfo>
> <b:schemaInfo count_positions_by_byte="false" standard="Flat File"
> root_reference="Root" child_delimiter_type="char"
> default_child_delimiter="," repeating_delimiter_type="hex"

> default_repeating_delimiter="0x0D 0x0A" parser_optimization="complexity"
> lookahead_depth="0" wrap_char_type="char" default_wrap_char=""" />

> repeating_delimiter_type="default" child_order="infix"
> child_delimiter_type="char" child_delimiter="," />

> <xs:element maxOccurs="unbounded" name="Record">

> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="2" structure="delimited"
> preserve_delimiter_for_empty_data="true"

> suppress_trailing_delimiters="false" repeating_delimiter_type="default"
> child_order="infix" child_delimiter_type="char" child_delimiter="," />

> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="StoreNum" type="xs:string">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="1" justification="left"
> wrap_char_type="default" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>
> <xs:element name="Date" type="xs:date">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="2" justification="left"

> datetime_format="yyyyMMdd" />

> </xs:appinfo>
> </xs:annotation>
> </xs:element>
> <xs:choice>

> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="3" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="XD9">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="1"
> structure="delimited" preserve_delimiter_for_empty_data="true"

> suppress_trailing_delimiters="false" tag_name=""XD9","
> repeating_delimiter_type="default" child_delimiter_type="char"
> child_order="infix" child_delimiter="," />

> tag_name=""XM8"," repeating_delimiter_type="default"
> child_delimiter_type="char" child_order="infix" child_delimiter="," />

> suppress_trailing_delimiters="false" tag_name=""XM9","
> repeating_delimiter_type="default" child_order="infix"
> child_delimiter_type="char" child_delimiter="," />

> <xs:element name="ItemNum" type="xs:integer">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="3"

> justification="left" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>

> <xs:element name="Price" type="xs:decimal">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="4"

> justification="left" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>

> </xs:sequence>
> </xs:complexType>
> </xs:element>

> </xs:choice>

> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>

> </xs:schema>

>
> --
> Dave
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matthew Roche" <Matthe...@discussions.microsoft.com> wrote in message

> news:BDFE68E8-62D8-4E03...@microsoft.com...

David Downing [MSFT]

unread,

Jun 14, 2004, 12:28:52 PM6/14/04

to

Matthew,

Glad I was able to help. I've only heard of one book that is coming out for
BizTalk 2004, and I'm not sure if it's been released yet (my guess is that
there are probably more books in progress). It's called "Microsoft Biztalk
Server 2004: Developer's Guide". I haven't seen a copy, so I'm not sure of
the content regarding flat file schema generation. Right now, your best bet
is probably to scan through this newsgroup and pick up ideas from there...
I've seen alot of good posts regarding flat file schemas.
--
Dave

This posting is provided "AS IS" with no warranties, and confers no rights.

"Matthew Roche" <Matthe...@discussions.microsoft.com> wrote in message

news:21E07C59-079C-4EE6...@microsoft.com...

Matthew Roche

unread,

Jun 14, 2004, 12:29:02 PM6/14/04

to

David:

I apologize for asking so many what must be basic questions, but I cannot find any useful information in the BizTalk Server documentation to help. Now that I have the document instances being successfully parsed and loaded into the system, I need to call SQL Server stored procedures with the values from the input docs. Each record type (XD9, XM8, XM9, etc.) from the input documents will have a corresponding stored procedure. I have already created the necessary schemas for these procedures, but am having difficulties with the maps to translate between the input schema and output schemas.

What I would like to do is to execute the appropriate stored procedures once for each record in the input documents, with the "correct" procedure being called for each input record. I believe I have things set up nearly correctly, but with my current maps each stored procedure is called once for each record in the input document, redardless of the record type. I am using the Looping functoid between the "Record" element in the input schema and the "prc_xd9_input" (or whatever is the root for the stored procedure in question) element in the output schema. In the BizTalk Server documentation for the Looping functoid I found the following text:

"Under certain conditions, some functoids might not behave as expected when they are used in a map with a Looping functoid. If a functoid meets the following conditions, it does not produce the expected results when used with the Looping functoid:

* There is a logical filter in effect that makes the loop occur only when the logical condition evaluates to True. "

This sounds like exatly what I want to do, but over an hour of searching on various forms of "logical filter" both in the BizTalk Server documentation and on Google have yielded no useful results.

If you could please point me in the right direction here I would be eternally grateful - Thanks in advance!

Matthew

"David Downing [MSFT]" wrote:

> Matthew,
>

> <?xml version="1.0" encoding="utf-16"?>
> <xs:schema xmlns="http://BizTalk_Research.TACO_Mockup_Schema"
> xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
> targetNamespace="http://BizTalk_Research.TACO_Mockup_Schema"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:appinfo>
> <b:schemaInfo count_positions_by_byte="false" standard="Flat File"
> root_reference="Root" child_delimiter_type="char"
> default_child_delimiter="," repeating_delimiter_type="hex"

> default_repeating_delimiter="0x0D 0x0A" parser_optimization="complexity"

> lookahead_depth="0" wrap_char_type="char" default_wrap_char=""" />

> repeating_delimiter_type="default" child_order="infix"
> child_delimiter_type="char" child_delimiter="," />

> <xs:element maxOccurs="unbounded" name="Record">

> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="2" structure="delimited"
> preserve_delimiter_for_empty_data="true"

> suppress_trailing_delimiters="false" repeating_delimiter_type="default"
> child_order="infix" child_delimiter_type="char" child_delimiter="," />

> </xs:appinfo>
> </xs:annotation>
> <xs:complexType>
> <xs:sequence>
> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="0" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="StoreNum" type="xs:string">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="1" justification="left"
> wrap_char_type="default" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>
> <xs:element name="Date" type="xs:date">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="2" justification="left"

> datetime_format="yyyyMMdd" />

> </xs:appinfo>
> </xs:annotation>
> </xs:element>
> <xs:choice>

> <xs:annotation>
> <xs:appinfo>
> <b:groupInfo sequence_number="3" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="XD9">
> <xs:annotation>
> <xs:appinfo>
> <b:recordInfo sequence_number="1"
> structure="delimited" preserve_delimiter_for_empty_data="true"

> suppress_trailing_delimiters="false" tag_name=""XD9","
> repeating_delimiter_type="default" child_delimiter_type="char"

> child_order="infix" child_delimiter="," />

> tag_name=""XM8"," repeating_delimiter_type="default"

> child_delimiter_type="char" child_order="infix" child_delimiter="," />

> suppress_trailing_delimiters="false" tag_name=""XM9","
> repeating_delimiter_type="default" child_order="infix"

> child_delimiter_type="char" child_delimiter="," />

> <xs:element name="ItemNum" type="xs:integer">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="3"

> justification="left" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>

> <xs:element name="Price" type="xs:decimal">
> <xs:annotation>
> <xs:appinfo>
> <b:fieldInfo sequence_number="4"

> justification="left" />
> </xs:appinfo>
> </xs:annotation>
> </xs:element>

> </xs:sequence>
> </xs:complexType>
> </xs:element>

> </xs:choice>

> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>

> </xs:schema>

>
> --
> Dave
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matthew Roche" <Matthe...@discussions.microsoft.com> wrote in message

> news:BDFE68E8-62D8-4E03...@microsoft.com...

David Downing [MSFT]

unread,

Jun 14, 2004, 1:13:00 PM6/14/04

to

Matthew,

This time I'm going to have to appologize... when it comes to mapping, I'm
quite the novice. In this case what I would do is create a new post with
the title referring to mapping rather than flat file (if the title contains
flat file, you won't get as many responses). Include in the post the the
description of the problem, the source and destination schemas as well as
the map. Also include the xml instance data rather than the flat file
instance data (you've already resolved the flat file parsing problem).
Hopefully someone out there can respond with the answer you're seeking.

Matthew Roche

unread,

Jun 14, 2004, 2:28:53 PM6/14/04

to

David:

Thanks for all of your help - I'll post a new request elsewhere.

Matthew

metin

unread,

Jun 16, 2004, 4:27:01 AM6/16/04

to

I think you don't need to use sequence.
Try to add this property: parser_optimization="complexity" into the schema.xsd by using a text editor, it is at the beginning of file:

<xs:annotation>
<xs:appinfo>
<b:schemaInfo count_positions_by_byte="false" standard="Flat File" root_reference="MyInputMsg" parser_optimization="complexity" codepage="65001" codelist_database="C:\.............

I hope this helps,
metin