How to avoid duplicates in XQuery results?

Question

This is newbie question. I am just start learning XQuery and XPath recently.

Consider this XML

<employees>
  <employee empid="1">
    <ename>KING</ename>
    <mgr/>
    <hiredate>17-11-1981</hiredate>
  </employee>
  <employee empid="2">
    <ename>BLAKE</ename>
    <mgr>7839</mgr>
    <hiredate>1-5-1981</hiredate>
    <test>
      <sub1>one</sub1>
      <sub2>two</sub2>
    </test>
  </employee>
</employees>

When I execute the following XQuery,

let $db := db:open("example")
for $item1 in $db/employees/employee,
    $item2 in $db/employees/employee/test
return ($item1/mgr,$item1/ename,$item2/sub1)

I got...

<mgr/>
<ename>KING</ename>
<sub1>one</sub1>
<mgr>7839</mgr>
<ename>BLAKE</ename>
<sub1>one</sub1>

The result that I am hoping to get is...

<mgr/>
<ename>KING</ename>
<mgr>7839</mgr>
<ename>BLAKE</ename>
<sub1>one</sub1>

This is because sub1 only exist in /employee/@empid='2'.

Can someone please point me to the right direction? Thanks

Answer 1

In your for expression

for $item1 in $db/employees/employee,
    $item2 in $db/employees/employee/test

$item1 iterates through all the employees/employee elements, and $item2 iterates through all the employees/employee/test elements, independent of what the current value of $item1 is.

To get what you need you might try this:

for $item1 in $db/employees/employee,
return ($item1/mgr,$item1/ename,
    for $item2 in $item1/test
    return $item2/sub1)

or, shorter:

$db/employees/employee/(mgr, ename, test/sub1)