Looping / fetching multiple rows ?

[michelb]

Hi, I'm using Kettle (w/ EAX) to generate XML files from our DB.

I've encoutered a situation where I'm not quite sure how to handle in Kettle. We have data like this that I need to insert in one file:

ID VALUE
--------------
ID1 A
ID1 B
ID1 C
ID2 D
ID3 E
ID3 F
...

and I'd like to end up with something like

<root>
<ID1>
<elem>A</elem>
<elem>B</elem>
<elem>C</elem>
</ID1>
<ID2>
<elem>D</elem>
</ID2>
<ID3>
<elem>E</elem>
<elem>F</elem>
</ID3>
...
</root>

(or each ID could be in separate files
file1: <root><ID1> ...</ID1></root>,
file2: <root><ID2>...</ID2></root>, ...)

What's the best way to deal with this? From what I can see the Kettle transforms allow me to deal with data on a 'row by row' method but I don't see a way to fetch more data for the same record (i.e. an obvious method would be to have the transform get all the IDs and then within that transform, fetch each value for that ID and output all that to one file however I don't see a mechanism for pulling more rows for the same record (seems like with 'Table Input', each row is treated separately)).

How can I do this? If one of the samples shows this, please point it out (I looked at what was there but didn't notice anything).

Thanks in advance,
Mike

[michelb]

If anyone has any ideas, I'm still looking for a solution.

From inside JS, is there a way to 'pop/push' the next data rows (i.e. with the 1st row, from JS, I can keep popping rows off until I get a different ID (and then push that row back)).

Let me know. Thanks

[hernanthiebaut]

try with the step normalizer rows

[michelb]

Thanks for the info, the 'row flattener' (and probably 'row denormalizer') kind of does what I'm looking at but it requires me to list all the fields beforehand (e.g. If I'm expecting 100 rows, I have to define 100 fields to hold the data). I'm looking for something a bit more generic which could handle any number of rows.

[hernanthiebaut]

you're right I meant row denormalize... I don`t know to much the step row flattener, read the documentation and later tell us...:D
good luck
THP