Skip to Content
0
Former Member
Oct 14, 2009 at 12:44 PM

Concatenating values in multiple rows to single field in Target.

864 Views

Hi all,

I have a scenario in which i am taking data from Database and giving output in the form of XML file.

My source format is as given below(Occurences are 1 for all the fields):

Row

Date

ShipName

CargoType

AgentName

Now i need to get the target format as given below(Occurences are 1 for all the fields):

Data

Date

Day

ExpectedArrivalTime

Ship

ExpCargo

Suppose i am getting source data like this

<Row>

<Date>2009-12-03 12:35:45</Date>

<ShipName>Vessel1</ShipName>

<CargoType>Cargo1</CargoType>

<AgentName>Agent1</AgentName>

</Row>

<Row>

<Date>2009-12-03 12:35:45</Date>

<ShipName>Vessel1</ShipName>

<CargoType>Cargo2</CargoType>

<AgentName>Agent1</AgentName>

</Row>

<Row>

<Date>2009-12-03 11:35:45</Date>

<ShipName>Vessel1</ShipName>

<CargoType>Cargo3</CargoType>

<AgentName>Agent1</AgentName>

</Row>

Outpt data should be in the format:

<Data>

<Date>2009-12-03</Date>

<Day>

<ExpectedArrivalTime>12:35</ExpectedArrivalTime>

<Ship>Vessel1</Ship>

<ExpCargo>Cargo1/Cargo2</ExpCargo>

</Day>

<Day>

<ExpectedArrivalTime>11:35</ExpectedArrivalTime>

<Ship>Vessel1</Ship>

<ExpCargo>Cargo3/Cargo2</ExpCargo>

</Day>

Could someone throw some light on how to concatenate the cargo field.

Thanks.