CODEWITHSUNDEEP
sql-serverxmlt-sqlxml-parsingcross-apply
sch
sch

Reputation: 27

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

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

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

Upvotes: 1

Views: 55

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

My suggestion uses XQuery in .nodes() to find the phone node, which is the primary, and the XQuery-function data() for your locations. This function will return with all included text parts separated by a blank (Btw: It's strange, that one cannot pass in the separator char...). In your case I expect solid-two-char location codes. Simply replace the blank with a comma and that's it...

SELECT re.value(N'IsActive[1]','bit') AS IsActive
      ,re.value(N'PID[1]','int') AS PID
      ,ph.value(N'PhoneNumber[1]','nvarchar(max)') AS PublicWorkPhone
      ,REPLACE(re.query(N'data(location/location-state)').value('.','nvarchar(max)'),' ',',') AS location
FROM #temp AS tmp
CROSS APPLY tmp.XMLData.nodes(N'/Report_Data/Report_Entry') AS A(re)
OUTER APPLY re.nodes(N'phone[IsPrimary=1]') AS B(ph);

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7928

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;

Upvotes: 0

Related Questions