XPath for second element with given child value?

Question

I have to find out the second Supplier with Chicago as the city.

I have the following table created.

CREATE TABLE Tb_Supplier(
XMLColumn XML)
GO

INSERT Tb_XPathQueryTable VALUES(
    '<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>');

I have to find out the second supplier with Chicago as the city.

I have tried the following code and a few variations:

SELECT XMLColumn.query('/SuppliersList/Supplier/City[text()="Chicago"]/../Supplier[2]')
FROM Tb_Supplier

When I use the above code without the /Supplier[2] it shows both of the suppliers with Chicago.

The output is supposed to have

  <Supplier name="Rosenfeld">
    <City>Chicago</City>
    <Product name="Computer"/>
    <Product name="Book"/>
    <Product name="Truck"/>
  </Supplier>

Answer 1

This XPath,

(/SuppliersList/Supplier[City="Chicago"])[2]

will select the second Supplier with "Chicago" as the City, as requested.