SQL Server 2012: parse XML then pick one of subnodes as well as concat all sub nodes

Question

I am trying to parse XML column and trying to do two things

• Iterate through multiple sub nodes of a node and pick one node based on a 1/0 flag value.
• combine all sub nodes to create a one combined field (delimited)

Runnable code block - SQL Server 2012:

create table #temp (XMLData xml)

insert into #temp (XMLData)
values ('
<Report_Data>
  <Report_Entry>
    <IsActive>1</IsActive>
    <PID>111</PID>
    <Languages>
      <Language>German</Language>
      <speak>Y</speak>
      <read>Y</read>
      <write>Y</write>
    </Languages>
    <Languages>
      <Language>Spanish</Language>
      <speak>Y</speak>
      <read>N</read>
      <write>N</write>
    </Languages>
    <phone>
      <PhoneNumber>(101)111-1111</PhoneNumber>
      <PhoneType>Work</PhoneType>
      <IsPrimary>1</IsPrimary>
    </phone>
    <phone>
      <PhoneNumber>(101)111-2222</PhoneNumber>
      <PhoneType>Mobile</PhoneType>
      <IsPrimary>0</IsPrimary>
    </phone>
  </Report_Entry>
  <Report_Entry>
    <IsActive>1</IsActive>
    <PID>222</PID>
    <phone>
      <PhoneNumber>(101)222-1111</PhoneNumber>
      <PhoneType>Mobile</PhoneType>
      <IsPrimary>0</IsPrimary>
    </phone>
  </Report_Entry>
  <Report_Entry>
    <IsActive>1</IsActive>
    <PID>333</PID>
    <phone>
      <PhoneNumber>(101)333-1111</PhoneNumber>
      <PhoneType>Phone</PhoneType>
      <IsPrimary>0</IsPrimary>
    </phone>
    <phone>
      <PhoneNumber>(101)333-2222</PhoneNumber>
      <PhoneType>Mobile</PhoneType>
      <IsPrimary>1</IsPrimary>
    </phone>
    <location>
      <location-state>NY</location-state>
    </location>
    <location>
      <location-state>DC</location-state>
    </location>
  </Report_Entry>
</Report_Data>
')

select 
      c.value('IsActive[1]','varchar(1)') as IsActive
    , c.value('PID[1]','varchar(5)') as PID
    , case when c.value('phone[1]/IsPrimary[1]','int') = 1 then c.value('phone[1]/PhoneNumber[1]','varchar(15)') end as  PublicWorkPhone /** this condition needs to look at all sub nodes. this stops at the first one.  **/
    , c.value('location[1]','varchar(2)') as location
from   
    #temp 
    cross apply #temp.XMLData.nodes('/Report_Data/Report_Entry') as y(c)

drop table #temp
GO

I get this:

IsActive PID   PublicWorkPhone location
-------- ----- --------------- --------
1        111   (101)111-1111   NULL
1        222   NULL            NULL
1        333   NULL            NY

However, I need this:

IsActive PID   PublicWorkPhone location
-------- ----- --------------- --------
1        111   (101)111-1111   NULL
1        222   NULL            NULL
1        333   (101)333-2222   NY,DC

For PID=333, the primary phone is (101)333-2222 and not null. Also, the location should be "NY,DC" not just NY.

I'd really appreciate any help you could offer achieving the result. Thank you

Answer 1

This should do the trick:

WITH prep AS 
(
  SELECT 
    c.value('(IsActive[1]/text())[1]','char(1)') as IsActive,
    c.value('(PID[1]/text())[1]','varchar(5)') as PID,
    c.value('(phone[IsPrimary=1]/PhoneNumber/text())[1]', 'varchar(15)') AS PublicWorkPhone,
    location = cc.value('(text())[1]', 'varchar(1000)')
  from #temp 
  CROSS APPLY #temp.XMLData.nodes('/Report_Data/Report_Entry') as y(c)
  OUTER APPLY c.nodes('location/location-state') AS z(cc)
)
SELECT
  IsActive, 
  PID,
  PublicWorkPhone,
  Location = 
  STUFF ((
    SELECT ',' + location
    FROM prep pp
    WHERE p.PID = pp.PID
    FOR XML PATH('')),1,1,'')
FROM prep p
GROUP BY IsActive, PID, PublicWorkPhone;