CODEWITHSUNDEEP
xmldatabasexpathxquery
user3495756
user3495756

Reputation: 3

How to compare values in different xml files using xquery?

I have these two xml files

cars.xml

<cars>
    <manufacturer>
        <model modelID="1">
          </model>
        <model modelID="2">
          </model>
        <model modelID="3">
          </model>
      </manufacturer>
    <manufacturer>
        <model modelID="4">
          </model>
        <model modelID="5">
          </model>
        <model modelID="6">
          </model>
      </manufacturer>
  </cars>

and a file called price.xml:

<price>
        <model modelID="1">
            <price>1000</price>
          </model>
        <model modelID="2">
            <price>3000</price> 
          </model>
        <model modelID="3">
            <price>2000</price>
          </model> 
        <model modelID="4">
            <price>2000</price>
          </model>
        <model modelID="5">
            <price>100</price>
          </model>
        <model modelID="6">
            <price>5000</price>
          </model>

</price>

The query that I want to perform is that for every manufacturer in cars.xml, I want to return the modelID of its most expensive model, however I cannot figure it out.

What I've tried is this:

for $manf in doc("cars.xml")//manufacturer
let $p := doc("price.xml")
where $manf/model/@modelID = $p/model/@modelID 
      and $p/model/price = (for $m in doc("cars.xml")//manufacturer
                            return max(for $pr in doc("price.xml")
                                       where $m/model/@modelID = $pr/model/@modelID
                                       return data($pr/model/price)))
return data($manf/model/@modelID)

I don't know if I'm anywhere close to right, but basically what I need to do is for every manufacturer, use its modelID's to somehow find which model is most expensive.

Upvotes: 0

Views: 269

Answers (1)

wst
wst

Reputation: 11773

let $p := doc("price.xml")
for $manf in doc("cars.xml")//manufacturer
let $models := $p//model[@modelID = $manf/model/@modelID]
let $max := max($models/price)
return $models[price = $max]

Upvotes: 1

Related Questions