Extract correct parent attribute value by XPath in Kettle / Spoon

Question

I've the following XML and use kettle spoon Get XML Data to extract:

<Company cname="ABC">
<Staffs>
    <Manager mname="John">
        <Subordinate sname="Apple">
            <PhoneNo>11111111</PhoneNo>
        </Subordinate>
        <Subordinate sname="Becky">
            <PhoneNo>22222222</PhoneNo>
        </Subordinate>
    </Manager>
    <Manager mname="Peter">
        <Subordinate sname="Cathy">
            <PhoneNo>33333333</PhoneNo>
        </Subordinate>
        <Subordinate sname="Doris">
            <PhoneNo>44444444</PhoneNo>
        </Subordinate>
    </Manager>
</Staffs>
</Company>

If I set the Loop XPath as /Company/Staffs/Manager/Subordinate , I got the incorrect Manager name (all managers are John!).

Mname: ../../*[name()='Manager']/@*[name()='mname']
Sname: @*[name()='sname']
PhoneNo: *[name()='PhoneNo']

|#|Mname|Sname|PhoneNo |
|-+-----+-----+--------|
|1|John |Apple|11111111|
|2|John |Becky|22222222|
|3|John |Cathy|33333333|
|4|John |Doris|44444444|

Please help, how to get the correct manager name?

------------------------
|#|Mname|Sname|PhoneNo |
|-+-----+-----+--------|
|1|John |Apple|11111111|
|2|John |Becky|22222222|
|3|Peter|Cathy|33333333|
|4|Peter|Doris|44444444|
------------------------

Answer 1

I don't know anythinh about kettle or spoon but IMO if you loop throuh "Subordinate" (so Subordinate is an actual context) you should use these xpaths:

Mname: parent::Manager/@mname

Sname: @sname

PhoneNo: PhoneNo