Keeping sibling records together: Complex flat file -> XML   

I have an HL7-like file that I'm trying to map to a simple XML schema. The 
problem that I'm having is that the flat file has a repeating segment 
(delimited) that contains multiple non-repeating subsegments (positional), 
and when the mapping takes place, the fields being pulled from the first 
subsegment are correct for each instance, but the values coming across for 
all subsequent subsegments are just the values from the first instance, 
repeated over and over.

So, for example (simplified):

Bob|Val1 Val2
Sally|Val3 Val4
....becomes...
<personlist>
  <person name="Bob" item1="Val1" item2="Val2" />
  <person name="Sally" item1="Val1" item2="Val2" />
</personlist>

I've tried every combination of looping and linking I can think of, and 
nothing seems to work.

If I use a looping functoid, I get something like this:
<personlist>
  <person name="Bob" item1="" item2="" />
  <person name="Sally" item1 = "" item2="" />
  <person name="" item1="Val1" item2="Val2" />
  <person name="" item1="Val3" item2="Val4" />
</personlist>

I realize this is difficult to envision without seeing the actual project, 
but maybe it will ring a bell with someone, or you can tell me what 
information I can provide that will make it easier to diagnose.

Thanks!
Troy

date: Sat, 25 Feb 2006 12:07:20 -0700   author:   Troy Young am

Re: Keeping sibling records together: Complex flat file -> XML   

If the person node is maxoccurs unbounded in both schema you should just 
link the name, item1 and item2 fields to each other.
No looping functoid is required. You can add a looping functoid between the 
person nodes of each schema and the result will be the same.

Another suggestion, if the personlist node is the same in both schema you 
can use the Mass Copy functoid to link the two.

If you could post your input an output schemas that would be useful, zipped 
and attached if possible

Greg


"Troy Young" <troysterr@nospam.nospam> wrote in message 
news:OqLd16jOGHA.2696@TK2MSFTNGP14.phx.gbl...
>I have an HL7-like file that I'm trying to map to a simple XML schema. The 
>problem that I'm having is that the flat file has a repeating segment 
>(delimited) that contains multiple non-repeating subsegments (positional), 
>and when the mapping takes place, the fields being pulled from the first 
>subsegment are correct for each instance, but the values coming across for 
>all subsequent subsegments are just the values from the first instance, 
>repeated over and over.
>
> So, for example (simplified):
>
> Bob|Val1 Val2
> Sally|Val3 Val4
> ...becomes...
> <personlist>
>  <person name="Bob" item1="Val1" item2="Val2" />
>  <person name="Sally" item1="Val1" item2="Val2" />
> </personlist>
>
> I've tried every combination of looping and linking I can think of, and 
> nothing seems to work.
>
> If I use a looping functoid, I get something like this:
> <personlist>
>  <person name="Bob" item1="" item2="" />
>  <person name="Sally" item1 = "" item2="" />
>  <person name="" item1="Val1" item2="Val2" />
>  <person name="" item1="Val3" item2="Val4" />
> </personlist>
>
> I realize this is difficult to envision without seeing the actual project, 
> but maybe it will ring a bell with someone, or you can tell me what 
> information I can provide that will make it easier to diagnose.
>
> Thanks!
> Troy
>

date: Sun, 26 Feb 2006 11:42:28 +1300   author:   Greg Forsythe

Re: Keeping sibling records together: Complex flat file -> XML   

Thanks for your reply.

The flat file I'm dealing with is actually a lot more complex than what I 
suggested, so I think you're right--I'll need to post the actual files, 
which I'll attach this message.

The attached ZIP file contains both schemas, the map I am currently using to 
translate the files, a sample input file, and the output that I am currently 
getting. There are two problems I'm trying to solve:

1) The claimlist/claim/editlist/edit/message element is wrong--it just 
repeats the first one it finds over and over again.
2) I'd like for the <editnumber/> elemements in editnumberlist to be unique 
(sort of like SELECT DISTINCT in SQL).

Ultimately, I would like to get rid of 
claimlist/claim/editlist/edit/message, and instead just reference the 
appropriate editnumberlist/editnumber element, for efficiency, but I'm sort 
of pursuing both solutions so that I can understand how to get it done in 
the future.

BTW...separate issue...we've been trying for months to find a fairly 
experienced BizTalk developer who lives in or is willing to relocate to 
Utah, and we've had no luck. Alternatively, we could probably use a BizTalk 
expert who is willing to moonlight and help us with problems like this. Do 
you have any suggestions on how to find such a person? Is it appropriate to 
post a job offer in these forums? Do you know anyone who might be 
interested? I ask you because you seem to be the one who has all the 
answers. :)

Thanks again,
Troy

"Greg Forsythe"  wrote in message 
news:%23j41wzlOGHA.2472@TK2MSFTNGP11.phx.gbl...
> If the person node is maxoccurs unbounded in both schema you should just
> link the name, item1 and item2 fields to each other.
> No looping functoid is required. You can add a looping functoid between 
> the
> person nodes of each schema and the result will be the same.
>
> Another suggestion, if the personlist node is the same in both schema you
> can use the Mass Copy functoid to link the two.
>
> If you could post your input an output schemas that would be useful, 
> zipped
> and attached if possible
>
> Greg
>
>
> "Troy Young" <troysterr@nospam.nospam> wrote in message
> news:OqLd16jOGHA.2696@TK2MSFTNGP14.phx.gbl...
>>I have an HL7-like file that I'm trying to map to a simple XML schema. The
>>problem that I'm having is that the flat file has a repeating segment
>>(delimited) that contains multiple non-repeating subsegments (positional),
>>and when the mapping takes place, the fields being pulled from the first
>>subsegment are correct for each instance, but the values coming across for
>>all subsequent subsegments are just the values from the first instance,
>>repeated over and over.
>>
>> So, for example (simplified):
>>
>> Bob|Val1 Val2
>> Sally|Val3 Val4
>> ...becomes...
>> <personlist>
>>  <person name="Bob" item1="Val1" item2="Val2" />
>>  <person name="Sally" item1="Val1" item2="Val2" />
>> </personlist>
>>
>> I've tried every combination of looping and linking I can think of, and
>> nothing seems to work.
>>
>> If I use a looping functoid, I get something like this:
>> <personlist>
>>  <person name="Bob" item1="" item2="" />
>>  <person name="Sally" item1 = "" item2="" />
>>  <person name="" item1="Val1" item2="Val2" />
>>  <person name="" item1="Val3" item2="Val4" />
>> </personlist>
>>
>> I realize this is difficult to envision without seeing the actual 
>> project,
>> but maybe it will ring a bell with someone, or you can tell me what
>> information I can provide that will make it easier to diagnose.
>>
>> Thanks!
>> Troy
>>
>
>

date: Mon, 27 Feb 2006 19:15:44 -0700   author:   Troy Young am

Re: Keeping sibling records together: Complex flat file -> XML   

Troy, I see what you mean by more complex.

To solve problem 1, I have added a ResultLineSegments record containing 
multiple ResultLineSegment records to your flat file schema. It still parses 
the flat file but produces a slightly different Xml output. This makes the 
map slightly easier than with a single ResultLineSegment with a repeating 
group of RLSeqX records.

To create the unique editnumber list was slightly harder, I used a C# 
scripting functoid with a global Hashtable but had lots of problems getting 
the output structure right
In the end I created an custom XSLT. Used the Validate Map to produce XSL 
and edited the <editnumberlist> bits.

I did not use a reference from the editlist to the editnumberlist, but this 
should be relatively easy using ID/IDREF

cheers
Greg


"Troy Young" <troysterr@nospam.nospam> wrote in message 
news:OZgwmzAPGHA.1288@TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
>
> The flat file I'm dealing with is actually a lot more complex than what I
> suggested, so I think you're right--I'll need to post the actual files,
> which I'll attach this message.
>
> The attached ZIP file contains both schemas, the map I am currently using 
> to
> translate the files, a sample input file, and the output that I am 
> currently
> getting. There are two problems I'm trying to solve:
>
> 1) The claimlist/claim/editlist/edit/message element is wrong--it just
> repeats the first one it finds over and over again.
> 2) I'd like for the <editnumber/> elemements in editnumberlist to be 
> unique
> (sort of like SELECT DISTINCT in SQL).
>
> Ultimately, I would like to get rid of
> claimlist/claim/editlist/edit/message, and instead just reference the
> appropriate editnumberlist/editnumber element, for efficiency, but I'm 
> sort
> of pursuing both solutions so that I can understand how to get it done in
> the future.
>
> BTW...separate issue...we've been trying for months to find a fairly
> experienced BizTalk developer who lives in or is willing to relocate to
> Utah, and we've had no luck. Alternatively, we could probably use a 
> BizTalk
> expert who is willing to moonlight and help us with problems like this. Do
> you have any suggestions on how to find such a person? Is it appropriate 
> to
> post a job offer in these forums? Do you know anyone who might be
> interested? I ask you because you seem to be the one who has all the
> answers. :)
>
> Thanks again,
> Troy
>
> "Greg Forsythe"  wrote in message
> news:%23j41wzlOGHA.2472@TK2MSFTNGP11.phx.gbl...
>> If the person node is maxoccurs unbounded in both schema you should just
>> link the name, item1 and item2 fields to each other.
>> No looping functoid is required. You can add a looping functoid between
>> the
>> person nodes of each schema and the result will be the same.
>>
>> Another suggestion, if the personlist node is the same in both schema you
>> can use the Mass Copy functoid to link the two.
>>
>> If you could post your input an output schemas that would be useful,
>> zipped
>> and attached if possible
>>
>> Greg
>>
>>
>> "Troy Young" <troysterr@nospam.nospam> wrote in message
>> news:OqLd16jOGHA.2696@TK2MSFTNGP14.phx.gbl...
>>>I have an HL7-like file that I'm trying to map to a simple XML schema. 
>>>The
>>>problem that I'm having is that the flat file has a repeating segment
>>>(delimited) that contains multiple non-repeating subsegments 
>>>(positional),
>>>and when the mapping takes place, the fields being pulled from the first
>>>subsegment are correct for each instance, but the values coming across 
>>>for
>>>all subsequent subsegments are just the values from the first instance,
>>>repeated over and over.
>>>
>>> So, for example (simplified):
>>>
>>> Bob|Val1 Val2
>>> Sally|Val3 Val4
>>> ...becomes...
>>> <personlist>
>>>  <person name="Bob" item1="Val1" item2="Val2" />
>>>  <person name="Sally" item1="Val1" item2="Val2" />
>>> </personlist>
>>>
>>> I've tried every combination of looping and linking I can think of, and
>>> nothing seems to work.
>>>
>>> If I use a looping functoid, I get something like this:
>>> <personlist>
>>>  <person name="Bob" item1="" item2="" />
>>>  <person name="Sally" item1 = "" item2="" />
>>>  <person name="" item1="Val1" item2="Val2" />
>>>  <person name="" item1="Val3" item2="Val4" />
>>> </personlist>
>>>
>>> I realize this is difficult to envision without seeing the actual
>>> project,
>>> but maybe it will ring a bell with someone, or you can tell me what
>>> information I can provide that will make it easier to diagnose.
>>>
>>> Thanks!
>>> Troy
>>>
>>
>>
>
>
>

date: Wed, 1 Mar 2006 00:26:37 +1300   author:   Greg Forsythe