Couldn't figure out an easy way to do this in BizTalk Mapper (2004)!
I have the following source and destination schemas:

SOURCE Schema:
--------------
Order
- ID
- PRICE
- PRODUCT_TYPE

Accessories
- ORDER_ID
- ACCESSORY_NAME
- PRICE

DESTINATION Schema:
-------------------

Order
- ORDER_ID
- ACCESSORY_COUNT
- PRICE
- PROD_TYPE

Order_Accessories
- ORDER_ID
- ACCESSORY_INDEX
- DESCN

Here I would like to achieve 2 things:
1. Fill ACCESSORY_COUNT in Order table of Destination schema.
- For this I should be able to calculate a record count of ACCESSORIES in
source schema for each ORDER.
So the resultant would look like -->
ORDER_ID ACCESSORY_COUNT
-------- ---------------
1           3
2           1
3           0
2. Assign an ACCESSORY_INDEX for each accessory of a particular order. So
the index would start from 0 again with a new order. Here's a sample of how
the result would look like in Order_Accessories table:
ORDER_ID   ACCESSORY_INDEX
--------   ---------------
1            1
1            2
1            3
2            1
Is there any way to keep track of the record index from ACCESSORY table in
source schema? But even if there is a way I would need to track the index fo
r
each order separately.
I am totally out of options of what could be done here. Is the BizTalk
mapper limited in what it can do in this type of situation?

Any help is higly appreciated,
Thanks,
Sudhir.

[ Post a follow-up to this message ]

Hi Sudhir,

BTS 2004 Mapper has wide range of funtoids by which you can do
transformation in enormous ways.

Try this:

1. Use "Record Count" functoid
2. Use "Index" functoid

Below links would be helpful:

http://msdn.microsoft.com/library/d...a5c47360095.asp

http://msdn.microsoft.com/library/d...
_map_bogx.asp


Regards,

Jay Kinker__


"Sudhir Darbha" wrote:

> Couldn't figure out an easy way to do this in BizTalk Mapper (2004)!
> I have the following source and destination schemas:
>
> SOURCE Schema:
> --------------
> Order
>   - ID
>   - PRICE
>   - PRODUCT_TYPE
>
> Accessories
>   - ORDER_ID
>   - ACCESSORY_NAME
>   - PRICE
>
> DESTINATION Schema:
> -------------------
>
> Order
>   - ORDER_ID
>   - ACCESSORY_COUNT
>   - PRICE
>   - PROD_TYPE
>
> Order_Accessories
>   - ORDER_ID
>   - ACCESSORY_INDEX
>   - DESCN
>
> Here I would like to achieve 2 things:
> 1. Fill ACCESSORY_COUNT in Order table of Destination schema.
>    - For this I should be able to calculate a record count of ACCESSORIES 
in
> source schema for each ORDER.
> So the resultant would look like -->
>    ORDER_ID ACCESSORY_COUNT
>    -------- ---------------
>       1           3
>       2           1
>       3           0
> 2. Assign an ACCESSORY_INDEX for each accessory of a particular order. So
> the index would start from 0 again with a new order. Here's a sample of ho
w
> the result would look like in Order_Accessories table:
>    ORDER_ID   ACCESSORY_INDEX
>    --------   ---------------
>       1            1
>       1            2
>       1            3
>       2            1
> Is there any way to keep track of the record index from ACCESSORY table in
> source schema? But even if there is a way I would need to track the index 
for
> each order separately.
> I am totally out of options of what could be done here. Is the BizTalk
> mapper limited in what it can do in this type of situation?
>
> Any help is higly appreciated,
> Thanks,
> Sudhir.

[ Post a follow-up to this message ]

Jay,
Thank you very much for the reply.
I looked at using "RecordCount" and "Index" functoids. But didn't find a way
of using them conditionally. For example, from the schema I posted, finding
number of accessory records for an order where Order ID = 1. Like this I nee
d
to find out the count separately for each Order.
And for the column Accessory_Index : Accessory_Index is 1,2,3 for Order_Id
'1' and the index numbering restarts for Order_Id '2'. With the help of Inde
x
functoid I am not sure how to restart the numbering for another order.
For your reference the following is the Source XML and desired Output XML:
=============
SOURCE XML:
=============
<CustomerPurchase>
<Orders>
<Order>
<ID>1</ID>
<Price>20.49</Price>
<Product_Type>Prod1</Product_Type>
</Order>
<Order>
<ID>2</ID>
<Price>100.50</Price>
<Product_Type>Product2</Product_Type>
</Order>
<Orders>
<Accessories>
<Accessory>
<Order_Id>1</Order_ID>
<Accessory_Name>Accs1</Accesory_Name>
<Price>12.00</Price>
</Accessory>
<Accessory>
<Order_Id>1</Order_ID>
<Accessory_Name>Accs2</Accesory_Name>
<Price>20.00</Price>
</Accessory>
<Accessory>
<Order_Id>1</Order_ID>
<Accessory_Name>Accs3</Accesory_Name>
<Price>10.00</Price>
</Accessory>
<Accessory>
<Order_Id>2</Order_ID>
<Accessory_Name>Prod2_Accs1</Accesory_Name>
<Price>10.00</Price>
</Accessory>
</Accessories>
============================
Now the Destination XML should look like :
============================
<Order>
<Order_ID>1</Order_ID>
<Accessory_Count>3</Accessory_Count>
<PRICE>20.49</PRICE>
<PROD_TYPE>Prod1</PROD_TYPE>
</Order>
<Order_ID>2</Order_ID>
<Accessory_Count>0</Accessory_Count>
<PRICE>100.50</PRICE>
<PROD_TYPE>Prod2</PROD_TYPE>
</Order>
<Order_Accessories>
<ORDER_ID>1</ORDER_ID>
<ACCESSORY_INDEX>1</ACCESSORY_INDEX>
<DESCN>ACCS1</DESCN>
</Order_Accessories>
<Order_Accessories>
<ORDER_ID>1</ORDER_ID>
<ACCESSORY_INDEX>2</ACCESSORY_INDEX>
<DESCN>ACCS2</DESCN>
</Order_Accessories>
<Order_Accessories>
<ORDER_ID>1</ORDER_ID>
<ACCESSORY_INDEX>3</ACCESSORY_INDEX>
<DESCN>ACCS3</DESCN>
</Order_Accessories>
<Order_Accessories>
<ORDER_ID>2</ORDER_ID>
<ACCESSORY_INDEX>1</ACCESSORY_INDEX>
<DESCN>PROD2_ACCS1</DESCN>
</Order_Accessories>
 ========================================
===

Thanks
Sudhir.

"Jay Kinker" wrote:
[vbcol=seagreen]
> Hi Sudhir,
>
> BTS 2004 Mapper has wide range of funtoids by which you can do
> transformation in enormous ways.
>
> Try this:
>
> 1. Use "Record Count" functoid
> 2. Use "Index" functoid
>
> Below links would be helpful:
>
> http://msdn.microsoft.com/library/d...a5c47360095.asp
>
> http://msdn.microsoft.com/library/d...og_map_bogx.asp
>
>
> Regards,
>
> Jay Kinker__
>
>
> "Sudhir Darbha" wrote:
> 

[ Post a follow-up to this message ]

Hi Sudhir,

Check out the proper combination of index values for Index functoid. By
default index functoid will only fetch data from the first occurance of any
sequence. So, you need to provide proper index values for proper reference.
Here are some hints.

You are probably using the the following parameters which is incorrect -
only the 1st resolve to the correct value so only the 1st iteration would
come in the output:
Data, 1
Data, 2
Data, 3

If you have sequences / Nodes with multiple occurences then you Would like
to iterate through all 3 subsequence using index functoids. index functoid
shoud have the following parameters since index parameter values start with
the record containing the field and work back to the root node:
Data, 1, 1, 1, 1, 1
Data, 1, 2, 1, 1, 1
Data, 1, 3, 1, 1, 1

Take a look at below link for index reference document:
http://msdn.microsoft.com/library/d...a5c47360095.asp

Now for getting value conditionally; you can use a combination of below
functoid in proper sequence and values:

index ==> equal ==> Value Mapping (flattering) ==> index
You may add/remove any functoid as per your requirement.

For details, read the MS documentation for each functoids.

I hope this approach helps in your case.

Regards,

Jay Kinker__


"Sudhir Darbha" wrote:
[vbcol=seagreen]
> Jay,
> Thank you very much for the reply.
> I looked at using "RecordCount" and "Index" functoids. But didn't find a w
ay
> of using them conditionally. For example, from the schema I posted, findin
g
> number of accessory records for an order where Order ID = 1. Like this I n
eed
> to find out the count separately for each Order.
> And for the column Accessory_Index : Accessory_Index is 1,2,3 for Order_Id
> '1' and the index numbering restarts for Order_Id '2'. With the help of In
dex
> functoid I am not sure how to restart the numbering for another order.
> For your reference the following is the Source XML and desired Output XML:
> =============
> SOURCE XML:
> =============
> <CustomerPurchase>
> <Orders>
> <Order>
> <ID>1</ID>
> <Price>20.49</Price>
> <Product_Type>Prod1</Product_Type>
> </Order>
> <Order>
> <ID>2</ID>
> <Price>100.50</Price>
> <Product_Type>Product2</Product_Type>
> </Order>
> <Orders>
> <Accessories>
> <Accessory>
> <Order_Id>1</Order_ID>
> <Accessory_Name>Accs1</Accesory_Name>
> <Price>12.00</Price>
> </Accessory>
> <Accessory>
> <Order_Id>1</Order_ID>
> <Accessory_Name>Accs2</Accesory_Name>
> <Price>20.00</Price>
> </Accessory>
> <Accessory>
> <Order_Id>1</Order_ID>
> <Accessory_Name>Accs3</Accesory_Name>
> <Price>10.00</Price>
> </Accessory>
> <Accessory>
> <Order_Id>2</Order_ID>
> <Accessory_Name>Prod2_Accs1</Accesory_Name>
> <Price>10.00</Price>
> </Accessory>
> </Accessories>
> ============================
> Now the Destination XML should look like :
> ============================
> <Order>
> <Order_ID>1</Order_ID>
> <Accessory_Count>3</Accessory_Count>
> <PRICE>20.49</PRICE>
> <PROD_TYPE>Prod1</PROD_TYPE>
> </Order>
> <Order_ID>2</Order_ID>
> <Accessory_Count>0</Accessory_Count>
> <PRICE>100.50</PRICE>
> <PROD_TYPE>Prod2</PROD_TYPE>
> </Order>
> <Order_Accessories>
> <ORDER_ID>1</ORDER_ID>
> <ACCESSORY_INDEX>1</ACCESSORY_INDEX>
> <DESCN>ACCS1</DESCN>
> </Order_Accessories>
> <Order_Accessories>
> <ORDER_ID>1</ORDER_ID>
> <ACCESSORY_INDEX>2</ACCESSORY_INDEX>
> <DESCN>ACCS2</DESCN>
> </Order_Accessories>
> <Order_Accessories>
> <ORDER_ID>1</ORDER_ID>
> <ACCESSORY_INDEX>3</ACCESSORY_INDEX>
> <DESCN>ACCS3</DESCN>
> </Order_Accessories>
> <Order_Accessories>
> <ORDER_ID>2</ORDER_ID>
> <ACCESSORY_INDEX>1</ACCESSORY_INDEX>
> <DESCN>PROD2_ACCS1</DESCN>
> </Order_Accessories>
>  ========================================
===
>
> Thanks
> Sudhir.
>
> "Jay Kinker" wrote:
> 

[ Post a follow-up to this message ]