SQL Select statement to shred XML with multiple siblings

I have an XML column that has multiple siblings that I am trying to retrieve with a SELECT statement using nodes() and values().  The xml is shown below:

    <Stores>
      <Location LocationId="NY-0001">
        <StoreType>"GasStation"</StoreType>
        <Employees>
          <EmployeeId>"111-11-1111"</EmployeeId>
        <Employees>
      </Location>

      <Location LocationId="TN-0001">
        <StoreId>"DineIn"</StoreId>
        <Employees>
          <EmployeeId>"222-22-2222"</EmployeeId>
          <EmployeeId>"333-33-3333"</EmployeeId>
          <EmployeeId>"444-44-4444"</EmployeeId>
          <EmployeeId>"555-55-5555"</EmployeeId>
        </Employees>
      </Location>

      <Location LocationId="TN-0006">
        <StoreId>"DineInDeliv"</StoreId>
        <Employees>
          <EmployeeId>"666-66-6666"</EmployeeId>
          <EmployeeId>"777-77-7777"</EmployeeId>
          <EmployeeId>"888-88-8888"</EmployeeId>
        </Employees>
      </Location>
<Stores>

I am trying to write a stored procedure that will return a table consisting of the following columns:

1.  Location
2.  Store Type
3. Employee ID

A table with one row per unique employee id. Location and Store type are repeated.

The  path expression for LocId and StoreType return the values I need.  The question is how should the path expression be constructed to return each Employee Id instead of only the first id?

Output of this select is shown below:

LocationId                           StoreType                            EmployeeId
------------                          -------------                          ---------------
NY-0001              "GasStation"              "111-11-111
TN-0001              "DineIn"                  "111-11-111
TN-0001              "DineIn"                  "111-11-111
TN-0001              "DineIn"                  "111-11-111
TN-0001              "DineIn"                  "111-11-111
TN-0006              "DineInDeliv"             "111-11-111
TN-0006              "DineInDeliv"             "111-11-111
TN-0006              "DineInDeliv"             "111-11-111

declare @cXml xml;
 
set @cXml = '<?xml version="1.0" encoding="utf-8"?>
<Stores>
      <Location LocationId="NY-0001">
        <StoreType>"GasStation"</StoreType>
        <Employees>
          <EmployeeId>"111-11-1111"</EmployeeId>
        </Employees>
      </Location>
 
      <Location LocationId="TN-0001">
        <StoreType>"DineIn"</StoreType>
        <Employees>
          <EmployeeId>"222-22-2222"</EmployeeId>
          <EmployeeId>"333-33-3333"</EmployeeId>
          <EmployeeId>"444-44-4444"</EmployeeId>
          <EmployeeId>"555-55-5555"</EmployeeId>
        </Employees>
      </Location>
 
      <Location LocationId="TN-0006">
        <StoreType>"DineInDeliv"</StoreType>
        <Employees>
          <EmployeeId>"666-66-6666"</EmployeeId>
          <EmployeeId>"777-77-7777"</EmployeeId>
          <EmployeeId>"888-88-8888"</EmployeeId>
        </Employees>
      </Location>
</Stores>'
 
select 
t.col_value.value( '(../.././@LocationId)[1]', 'char(15)') as LocId,
t.col_value.value( '(../../StoreType)[1]', 'char(20)') as StoreType,
t.col_value.value( '(//EmployeeId)[1]', 'char(11)') as EmployeeId
from @cXML.nodes('//Employees/EmployeeId') as t(col_value)

Select allOpen in new window