Pentaho PDI - XPath syntax for XML Join step

Question

I am trying to join xml code with Pentaho PDI in a transformation with a "Add XML" step, which add some fields with a "Root XML Element" set as "Node" (like below) and a "XML Join" step.

I want to insert some fields with the same data into each and every "Node".

<Rootnode>
 <Node>
 <Node>
 <Node>
</Rootnode>

The problem is that, no matter what XPath expression I try, the fields I want to insert are only inserted in the first node. Expressions like "RootNode/Node" or "//Node" are not working.

This is the result I get:

<RootNode>
   <Node>
    <inserted field>
  <Node>
  <Node>
</RootNode>

This is what I want to get:

<RootNode>
  <Node>
    <inserted field>
  <Node>
    <inserted field>
  <Node>
    <inserted field>
</RootNode>

Questions: can the XML-join step only join code into one explicitly specified node or is there a XPath-expression I can use in the XML-join step´s XPath Statement input to insert the code into all nodes of my choice?

(I dont think a complex join with a comparion field is appropriate because I dont have anything to compare with.)

Rishu Shrivastava Rishu Shrivastava · Accepted Answer · 2015-03-01T15:38:18

Yes, you can read XML nodes in a recurring fashion. Check this : How to extract XML node values and from recurring nodes in pentaho? solution to do it. You need to properly define the XPATH in the Fields Section. The use of "."(dot) is important in here.

Now for your question, you need to apply the same logic as above but with slight change. Check the image below:

enter image description here

What i have done here is :

Firstly, get the Rootnode structure, which i will use it to join later (the first row of the ktr in the image)
Secondly, i read all the Node in a recuring fashion as explained in the above link. Check the image below:

enter image description here

Add a constant field. This is your new Field Node. i have used newField as the node name.

In the "Add XML" part, add this new field to the Node section.

XML Join : In this step, give the joining condition as //Rootnode. This will join with the Rootnode coming in from "point #1".

enter image description here

Finally generate the XML output (target.xml).

I have placed a gist of the .ktr as explained above. Please have a look.

Note: The source.xml file is same as in the question.

Handling complex XML structure:

As highlighted in the comments below, the above approach would fail when it comes to handling complex xml structure. So in order to achieve this, we would require to use 'XML Input Stream (StAX)' step, which uses the StAX parser to easily read the complex XML structures.

I have documented the same in this blog here along with the gist. Please check this out !! I assume this would work on your data set too.

Hope it helps :)

Pentaho PDI - XPath syntax for XML Join step

1 Answers