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)

Open in new window

MDKIMZEYAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MDKIMZEYAuthor Commented:
Solution is

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( 'self::EmployeeId', 'char(11)') as EmployeeId
from @cXML.nodes('//Employees/EmployeeId') as t(col_value)
Output is below:
LocId              StoreType          EmployeeId
NY-0001         "GasStation"         "111-11-111
TN-0001         "DineIn"             "222-22-222
TN-0001         "DineIn"             "333-33-333
TN-0001         "DineIn"             "444-44-444
TN-0001         "DineIn"             "555-55-555
TN-0006         "DineInDeliv"        "666-66-666
TN-0006         "DineInDeliv"        "777-77-777
TN-0006         "DineInDeliv"        "888-88-888

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.