sql server - XPath Query to find supplier name through an XML table

Question

I am trying to find out the cities where there is a supplier with the name of Herman. We are using XPath through Microsoft SQL Server Management Studio.

I am not a CIS major and I have no idea where to start. So that's why I'm asking for help...

I have the following table created:

CREATE TABLE Tb_Supplier(
XMLColumn XML)
GO

INSERT Tb_Supplier 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>');

The output is supposed to show:

<City>Chicago</City>

I got the it to work by the following:

SELECT XMLColumn.query('/SuppliersList/Supplier[@name="Herman"]/City')
FROM Tb_Suppliers

---

I am kind of getting the grasp of things. Currently I am having trouble how to find out the following: List the products are TV's, and are offered in Madison. Obviously the output will be since it is looking for TV.

I have this so far, I don't know what I'm doing wrong as this makes the most sense to me.

SELECT XMLColumn.query('/SuppliersList/Supplier/Product[@name="TV"]/../City[@name="Madison"]/../Product')
FROM Tb_Suppliers

Answer 1

It depends whether you know, that there is exactly one "Herman" in your list. Then it's a pure call on .value() with the appropriate XPath:

SELECT XMLColumn.value(N'(/SuppliersList/Supplier[@name="Herman"]/City/text())[1]',N'nvarchar(max)') AS Herman_City
FROM Tb_Supplier 

If you expect more than one, you need .nodes() to get a derived table and value() on the relative path (below the node coming from .nodes()):

SELECT AllHerman.cities.value(N'(City/text())[1]',N'nvarchar(max)') AS Herman_City
FROM Tb_Supplier
CROSS APPLY XMLColumn.nodes(N'/SuppliersList/Supplier[@name="Herman"]') AS AllHerman(cities)