Mapping flat file of orders (one line for each order) to order XML with multiple items

Web Server Talk > Web Servers reviews > BizTalk Server > BizTalk Server Tools > Mapping flat file of orders (one line for each order) to order XML with multiple items

Last Thread Next Thread Next

Mapping flat file of orders (one line for each order) to order XML with multiple items

Jeremy Lukensmeyer

02-08-04 01:42 PM

Hi,

I'm setting up a mapping to transform a tab-delimited file of orders
and items to XML.  I've setup an XML import for the tab-delimited file
so that I can perform the mapping.  The first case I need help with is
when I have multiple lines of the source for one order and I want
those to become one order with multiple line items in my XML.

Here's a simplified example:
Format:Order ID|Name|SKU
1|Jeremy|12
1|Jeremy|15
2|Bob|14
2|Bob|19
3|Xavier|20
4|Jacob|25

I would like this to come out as:
<Orders>
<Order>
<OrderID>1</OrderID>
<Name>Jeremy</Name>
<LineItems>
<LineItem>
<SKU>12</SKU>
</LineItem>
<LineItem>
<SKU>15</SKU>
</LineItem>
</LineItems>
</Order>
<Order>
<OrderID>2</OrderID>
<Name>Bob</Name>
<LineItems>
<LineItem>
<SKU>14</SKU>
</LineItem>
<LineItem>
<SKU>19</SKU>
</LineItem>
</LineItems>
</Order>
<Order>
<OrderID>3</OrderID>
<Name>Xavier</Name>
<LineItems>
<LineItem>
<SKU>20</SKU>
</LineItem>
</LineItems>
</Order>
<Order>
<OrderID>4</OrderID>
<Name>Jacob</Name>
<LineItems>
<LineItem>
<SKU>25</SKU>
</LineItem>
</LineItems>
</Order>

Also, on each line of the flat file I have billing and shipping
information.  This information needs to go into 2 Consumer records in
each order, each having a different Type (where Type is a field in
Consumer).  I was able to accomplish this by doing two mappings.
First from my flat file to an intermediate format where billing and
shipping are two different records.  Then using the looping functiod
to pull the billing and shipping records into the Consumer record.
The problems I saw were trying to create two distinct records of
Consumer, setting the Type based on which fields it was taking, and
then grouping the fields together.  Is there a way I can do this in
one mapping?  If so how?

Example input:
Format: Order ID|Billing Name|Shipping Name
1|Jeremy|Jeremy
2|Sam|Bill
3|Joan|Joan

Current Next Step:
<Orders>
<Order>
<OrderID>1</OrderID>
<Billing>
<Name>Jeremy</Name>
</Billing>
<Shipping>
<Name>Jeremy</Name>
</Shipping>
</Order>
<Order>
<OrderID>2</OrderID>
<Billing>
<Name>Sam</Name>
</Billing>
<Shipping>
<Name>Bill</Name>
</Shipping>
</Order>
<Order>
<OrderID>3</OrderID>
<Billing>
<Name>Joan</Name>
</Billing>
<Shipping>
<Name>Joan</Name>
</Shipping>
</Order>

This is finally mapped to:
<Orders>
<Order>
<OrderID>1</OrderID>
<Consumer Type="Billing">
<Name>Jeremy</Name>
</Consumer>
<Consumer Type="Shipping">
<Name>Jeremy</Name>
</Consumer>
</Order>
<Order>
<OrderID>2</OrderID>
<Consumer Type="Billing">
<Name>Sam</Name>
</Consumer>
<Consumer Type="Shipping">
<Name>Bill</Name>
</Consumer>
</Order>
<Order>
<OrderID>3</OrderID>
<Consumer Type="Billing">
<Name>Joan</Name>
</Consumer>
<Consumer Type="Shipping">
<Name>Joan</Name>
</Consumer>
</Order>


Thanks,

Jeremy

[ Post a follow-up to this message ]