CODEWITHSUNDEEP
xpathxqueryxquery-sqlxquery-3.0xquery-update
Leon
Leon

Reputation: 23

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.

Upvotes: 2

Views: 48

Answers (1)

Leo W&#246;rteler
Leo W&#246;rteler

Reputation: 4241

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>

Upvotes: 1

Related Questions