|
Home > Archive > BizTalk Server General > May 2006 > BizTalk Mapping Question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BizTalk Mapping Question
|
|
| Sudhir Darbha 2006-05-24, 1:14 pm |
| 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 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.
| |
| Jay Kinker 2006-05-25, 7:14 am |
| 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:
> 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 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.
| |
| Sudhir Darbha 2006-05-29, 5:21 pm |
| 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 need
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 Index
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:
>
| |
| Jay Kinker 2006-05-29, 5:21 pm |
| 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 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 need
> 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 Index
> 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:
>
|
|
|
|
|