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?
 
MDKIMZEYConnect With a Mentor Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.