Reading both attributes and nodes at the same time in Kettle / Spoon

Question

I'm using kettle and trying to load both the attribute and node values from an xml document.

<Colors>
  <Color code="123">blue</Color>
  <Color code="234">black</Color>
  <Color code="456">green</Color>
</Colors>

If I set the loop XPath to Colors I will only get one row but it will read both the code and the value.
example:

Code  | Color
123   | blue

But if I set the XPath to the Color I will get 3 rows, but it will not read the value for each item.
example:

Code 
123
234
456

How do I read all elements and both the attribute and node value in one pass?

Thanks.

Answer 1

Use a "Get Data From XML step" (input category) Set the Loop XPath to /Colors/Color In the fields tab, hit "Get Fields". This will add a row for

Name    XPath   Element Type Format Length Precision CurrencyDecimal Group Trim type Repeat
code    @code   Node Integer       none N

Now, for the value of the elements, add a new row manually in the grid like this:

Name    XPath   Element Type Format Length Precision CurrencyDecimal Group Trim type Repeat
value   text()  Node String       none N

In other words, use the XPath standard text() function to extract the text content from the context node.

As always, use Preview rows to check the result. I get this:

code    value
123 blue
234 black
456 green