xpathxqueryxquery-sqlxquery-3.0xquery-update

How to write an inquiry flwor xquery?


I have 5 cars, each car with 5 owners, I need to return those owners whose nation does not appear in the owners of other cars (can appear in the owners of the same car):

<root>
  <car>
   <id>1</id>
   <owner>
            <name>George Smith</name>
            <nation>USA</nation>
   </owner>
   <owner>
            <name>Carolina Herre</name>
            <nation>USA</nation>
   </owner>
   <owner>
            <name>Martha Belar</name>
            <nation>Denmark</nation>
   </owner>
   <owner>
            <name>Fernando Izza</name>
            <nation>Italy</nation>
   </owner>
   <owner>
            <name>George Smith</name>
            <nation>Italy</nation>
   </owner>
 </car>
 <car>
   <id>2</id>
   <owner>
            <name>George Gelar</name>
            <nation>USA</nation>
   </owner>
   <owner>
            <name>Gema Bio</name>
             <nation>Spain</nation>
    </owner>
    <owner>
             <name>Peter Vdf</name>
            <nation>Denmark</nation>
    </owner>
    <owner>
            <name>Felipe Rodriguez</name>
            <nation>Denmark</nation>
    </owner>
    <owner>
            <name>George Smith</name>
            <nation>USA</nation>
    </owner>
</car>
<car>
...
</car>
<car>
...
</car>
<car>
...
</car>
</root>

That is, given that xml, the output would be:

<owner>
    <name>Fernando Izza</name>
    <nation>Italy</nation>
    <carID>1</carID>
</owner>
<owner>
    <name>George Smith</name>
    <nation>Italy</nation>
    <carID>1</carID>
</owner>
<owner>
    <name>Gema Bio</name>
    <nation>Spain</nation>
    <carID>2</carID>
</owner>

I tried to get the list of countries that do not appear in several cars, and then get those owners who are from those countries, but I do not know how to get it.


Solution

  • You can just group the owners by their nation and then look upwards to see if the nation occurs below more than one car:

    for $owner in doc("cars.xml")//car/owner
    group by $nation := $owner/nation
    where count($owner/..) eq 1
    for $o in $owner
    return <owner>{
      $o/*,
      <carID>{$o/../id/text()}</carID>
    }</owner>