Dynamic XML parsing in Pentaho Kettle

Question

I have been using STAX parser for quite long in pentaho Kettle. But suddenly I got a situation which is weird. Earlier the XML files were having pre-defined levels like :

<A>
     <TRADE a="1" b="2">
        <TRADE a="3" b="4">
        </TRADE>
     </TRADE>
   </A>

 OR

   <A>
     <TRADE a="100" b="200">
       <TRADE a="1" b="2">
          <TRADE a="3" b="4">
             <TRADE a="5" b="6"> 
             </TRADE>
          </TRADE>
       </TRADE>
    </TRADE>
   </A>

If file is comprising of two levels of trade then it was known in advance and the same for three or four levels(four being the highest). Accordingly the Xpath was mentioned in STAX parser(A/TRADE/TRADE/TRADE for level three and so on).

Expected output :

In first case - Two entries(rows) in Trade Table one for parent Trade other for child Trade. In second case - Four entries(rows) in Trade Table - establishing parent child relationship.

How to do this dynamically without knowing the number of Trades(Depth)

But now the file can have any level of TRADE(min 1 max 15). I am baffled as to how to dynamically parse the TRADE file using STAX parser in pentaho kettle.

Any guidance will be extremely helpful.

Regards, Vikas

Answer 1

With this Transformation:

Filter rows:

Row denormaliser:

Gives the following output: