Selecting "at least 2" XPath Expression

Question

I need to write a XPath expression to find the cities with at least 2 suppliers based on the data below (the expression should return Chicago and Madison because they each have 2 suppliers):

<SuppliersList>
  <Supplier name="Joe">
    <City>Paris</City>
    <Product name="Airplane"/>
    <Product name="Milk"/>
    <Product name="TV"/>
    <Product name="Orange"/>
 </Supplier>
  <Supplier name="Herman">
    <City>Chicago</City>
    <Product name="Orange"/>
 </Supplier>
 <Supplier name="Bernstein">
    <City>Madison</City>
    <Product name="Truck"/>
    <Product name="TV"/>
  </Supplier>
 <Supplier name="Hunter">
    <City>Wausau</City>
  </Supplier>
  <Supplier name="Mayer">
    <City>Madison</City>
  </Supplier>
  <Supplier name="Rosenfeld">
    <City>Chicago</City>
    <Product name="Computer"/>
    <Product name="Book"/>
    <Product name="Truck"/>
  </Supplier>
</SuppliersList>

Any help would be greatly appreciated

Answer 1

//Supplier[City = following::Supplier/City]/City

Explanation:

• Get all Supplier elements
  • Where City element is equal to any Supplier/City element below current node
• And take City element

Of course, that is much more easy in a XSLT template

EDIT

Now I see you're using XQuery, not XPath:

DECLARE @x xml
SET @x = '
<SuppliersList>
  <Supplier name="Joe">
    <City>Paris</City>
    <Product name="Airplane"/>
    <Product name="Milk"/>
    <Product name="TV"/>
    <Product name="Orange"/>
 </Supplier>
  <Supplier name="Herman">
    <City>Chicago</City>
    <Product name="Orange"/>
 </Supplier>
 <Supplier name="Bernstein">
    <City>Madison</City>
    <Product name="Truck"/>
    <Product name="TV"/>
  </Supplier>
 <Supplier name="Hunter">
    <City>Wausau</City>
  </Supplier>
  <Supplier name="Mayer">
    <City>Madison</City>
  </Supplier>
  <Supplier name="Rosenfeld">
    <City>Chicago</City>
    <Product name="Computer"/>
    <Product name="Book"/>
    <Product name="Truck"/>
  </Supplier>
</SuppliersList>'
SELECT @x.query('
    let $cities := //City
    for $city in distinct-values($cities)
        where count($cities[. eq $city]) >= 2
        return $city
    ')