CODEWITHSUNDEEP
xmlxpathxquerybasex
beyonddc
beyonddc

Reputation: 1256

How to avoid duplicates in XQuery results?

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

Upvotes: 2

Views: 456

Answers (2)

Mathias M&#252;ller
Mathias M&#252;ller

Reputation: 22617

Here is one way to do it. Before returning, check with an if statement whether test/sub1 exists or not.

I slightly changed the for clause as well, removing the db:open() function, but it's easy to add it back in.

for $employee in /employees/employee
return
if ($employee/test/sub1)
then
($employee/mgr, $employee/ename, $employee/test/sub1)
else
($employee/mgr, $employee/ename)

and the result will be

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

You said there is more than one ways to do this, can you please describe further

A slightly different approach would be to use an if/then/else XPath expression and unconditionally returning this expression:

for $employee in /employees/employee
return
($employee/mgr, $employee/ename, if ($employee/test/sub1) then $employee/test/sub1 else '')

And the answer by CiaPan shows yet another way, this time by nesting for clauses. In my opinion, it is a little less straightforward but it works nevertheless. CiaPan also shows how a single XPath expression can potentially solve the problem - which is very straightforward!

Upvotes: 1

CiaPan
CiaPan

Reputation: 9570

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)

Upvotes: 3

Related Questions