| Author |
Biztalk 2004 mapping - Many to One
|
|
|
| Hi All,
How do I map many source records to single target record
in mapping
I have input/source file schema which has multiple
records of same type.
I Need to transform it to a target schema which will have
single record. Ofcourse few fields are to be picked up
from 1st record(or could be from any record as they are
same for all the records) and few fields need to have
summarised values(based on key value from source record)
as follows:
1.Sum(Quantity) WHERE keyfield = 'x'
2.COUNT of DISTINCT records on a keyfield2 WHERE
keyfield='y'
Thanks in advance.
Thanks
-Ragav
| |
| Greg Forsythe 2004-11-07, 5:48 pm |
| One way of doing this is a scripting functiod using Inline XSLT and the
XPath 'sum()' and 'count()' functions.
e.g.
<total>
<xsl:value-of select="sum(/*[local-name='root']/*[local-name='record' and
@keyfield1='x']/*[local-name='Quantity'])"/>
</total>
<quantity>
<xsl:value-of select="count(/*[local-name='root']/*[local-name='record' and
@keyfield2='y'])"/>
</quantity>
The above is a simple example of using the sum and count XPath functions and
makes assumptions about the Xml structure.
This should work with any Xml, the tricky part is constructing the XPath to
select the nodes you wish to sum or count.
Greg
"Ragav" <anonymous@discussions.microsoft.com> wrote in message
news:46a801c4c35b$583cb3f0$a301280a@phx.gbl...
> Hi All,
>
> How do I map many source records to single target record
> in mapping
>
> I have input/source file schema which has multiple
> records of same type.
> I Need to transform it to a target schema which will have
> single record. Ofcourse few fields are to be picked up
> from 1st record(or could be from any record as they are
> same for all the records) and few fields need to have
> summarised values(based on key value from source record)
> as follows:
> 1.Sum(Quantity) WHERE keyfield = 'x'
> 2.COUNT of DISTINCT records on a keyfield2 WHERE
> keyfield='y'
>
> Thanks in advance.
>
> Thanks
> -Ragav
>
| |
|
| Thanks Greg.
Qn1:
When I map Source with target schema I am getting many
records in destination file as there are many records in
source file. How to get Many to One ..? I think its the
trick in schema definition of source and target schema.
I have 10 columns in my source schema. The source has
same values (with respect to each field) in 7 fields in
all the records(The remaining fields are used for summary
and conditional calculation)
I need to get 7 fields to the target file as a single
record.
Qn2:
As I did not do much in XSLT would like to clarify little
more.
Is that the COUNT in XSLT will do the Distinct of
keyfield values?
Some light on how to test .. this..with script functoid
(s) in a map will help.
Thanks in advance,
>-----Original Message-----
>One way of doing this is a scripting functiod using
Inline XSLT and the
>XPath 'sum()' and 'count()' functions.
>e.g.
><total>
><xsl:value-of select="sum(/*[local-name='root']/*[local-
name='record' and
>@keyfield1='x']/*[local-name='Quantity'])"/>
></total>
><quantity>
><xsl:value-of select="count(/*[local-name='root']/*
[local-name='record' and
>@keyfield2='y'])"/>
></quantity>
>
>The above is a simple example of using the sum and count
XPath functions and
>makes assumptions about the Xml structure.
>This should work with any Xml, the tricky part is
constructing the XPath to
>select the nodes you wish to sum or count.
>
>Greg
>
>
>
>"Ragav" <anonymous@discussions.microsoft.com> wrote in
message
>news:46a801c4c35b$583cb3f0$a301280a@phx.gbl...
record[vbcol=seagreen]
have[vbcol=seagreen]
record)[vbcol=seagreen]
>
>
>.
>
| |
|
| Thanks Greg.
Let me check your samples first.
But am not able to see your attachement from the
Microsoft newsgroup.
I got a chance to see it in http://www.webservertalk.com,
but still its rendered as a text characters on the page
itself.
Do i need to check some other sites to get the attachment?
Thanks,
Ragav
>-----Original Message-----
>1) You can use the Index functiod to map the 7 fixed
fields.
>2) I am unsure of what you mean by Distinct
>
>I have attached a sample with 2 schemas and a map which
may be of some
>assistance, if not can you mail me your input and output
schemas and a brief
>description of the map requirements.
>
>Greg
>
>
>"Ragav" <anonymous@discussions.microsoft.com> wrote in
message
>news:078101c4c5ec$812277d0$3501280a@phx.gbl...
in[vbcol=seagreen]
summary[vbcol=seagreen]
little[vbcol=seagreen]
[local-[vbcol=seagreen]
count[vbcol=seagreen]
up[vbcol=seagreen]
are[vbcol=seagreen]
have[vbcol=seagreen]
>
>
>
| |
| Greg Forsythe 2004-11-22, 2:46 am |
| Send me an email, I'll send them to you.
Greg
"Ragav" <anonymous@discussions.microsoft.com> wrote in message
news:0ed301c4d03a$5c7ea220$3501280a@phx.gbl...[vbcol=seagreen]
> Thanks Greg.
> Let me check your samples first.
> But am not able to see your attachement from the
> Microsoft newsgroup.
> I got a chance to see it in http://www.webservertalk.com,
> but still its rendered as a text characters on the page
> itself.
> Do i need to check some other sites to get the attachment?
>
> Thanks,
> Ragav
>
> fields.
> may be of some
> schemas and a brief
> message
> in
> summary
> little
> [local-
> count
> up
> are
> have
| |
|
| Hi Greg,
can you send me the attach at :
fabio.*remove*ucosich@deltadator.it
Tanks in advance,
Fabio
"Greg Forsythe" wrote:
> 1) You can use the Index functiod to map the 7 fixed fields.
> 2) I am unsure of what you mean by Distinct
>
> I have attached a sample with 2 schemas and a map which may be of some
> assistance, if not can you mail me your input and output schemas and a brief
> description of the map requirements.
>
> Greg
>
>
> "Ragav" <anonymous@discussions.microsoft.com> wrote in message
> news:078101c4c5ec$812277d0$3501280a@phx.gbl...
>
>
>
|
|
|
|