Parse XML Data

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  March 7, 2019 at 11:26 am

  #370951

  Hi I'm trying to parse out the "Minutes in this XML Data

  <Data>
  <Interventions_x0020_Provided>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>On-site (Observations, Consultation)</Intervention>
    <Provided>true</Provided>
    <Minutes>35</Minutes>
    <Describe>Completed observation. Discussed with Janet (teacher) how Aisling's behavior has been.</Describe>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Behavior Plan Meeting</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Assessment/ Screening</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Coaching</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Telephone Consultation or Coaching</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Case Management (Referrals, care coordination)</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Documentation (Reports, Materials)</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Parent Intake/ home or phone</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Travel</Intervention>
    <Provided>true</Provided>
    <Minutes>45</Minutes>
    </Interventions_x0020_Provided_x0020_SubTable>
    <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Training</Intervention>
    </Interventions_x0020_Provided_x0020_SubTable>
  </Interventions_x0020_Provided>

  So that I get 
  On-site (Observations, Consultation)    Travel
  35                                                           45 

  I tried below but not getting the correct positioning 

   CASE    WHEN Data.value('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable/Intervention)[1]', 'nvarchar(100)') = 'On-site (Observations, Consultation)'     
   AND Data.value('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable/Provided)[1]', 'nvarchar(100)') = 'true'   
   THEN Data.value('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable/Minutes)[1]', 'nvarchar(100)')       END AS [On-site_Minutes], 
  etc...  (below is position 9, for the data above 
   CASE    WHEN Data.value('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable/Intervention)[9]', 'nvarchar(100)') = 'travel'     
   AND Data.value('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable/Provided)[9]', 'nvarchar(100)') = 'true'   
   THEN Data.value('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable/Minutes)[9]', 'nvarchar(100)')       END AS [On-site_Minutes],  
  etc... 

  sorry for the vagueness...

  Thanks

• handkot

  SSCertifiable

  Points: 5250

  More actions

  March 13, 2019 at 7:30 am

  #2024320

  maybe this will help

  declare @xml as xml = '
<Data>
 <Interventions_x0020_Provided>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>On-site (Observations, Consultation)</Intervention>
   <Provided>true</Provided>
   <Minutes>35</Minutes>
   <Describe>Completed observation. Discussed with Janet (teacher) how Aisling''s behavior has been.</Describe>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Behavior Plan Meeting</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
   <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Assessment/ Screening</Intervention>
   </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Coaching</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
   <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Telephone Consultation or Coaching</Intervention>
   </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Case Management (Referrals, care coordination)</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Documentation (Reports, Materials)</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Parent Intake/ home or phone</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Travel</Intervention>
   <Provided>true</Provided>
   <Minutes>45</Minutes>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Training</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  </Interventions_x0020_Provided>
 </Data>'

  --just get the Intervention
Select
 x.col.value('(Intervention/text())[1]', 'varchar(100)')
 , x.col.value('(Minutes/text())[1]', 'integer')
 , x.col.value('(Intervention/text())[1]', 'varchar(100)')
From
 @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
--Where
-- x.col.exist('(Provided[text()[1] = "true"])')=1--method 1
Select
 Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'On-site (Observations, Consultation)' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Behavior Plan Meeting' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Assessment/ Screening' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Coaching' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Telephone Consultation or Coaching' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Case Management (Referrals, care coordination)' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Documentation (Reports, Materials)' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Parent Intake/ home or phone' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Travel' Then x.col.value('(Minutes/text())[1]', 'integer') End)     
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Training' Then x.col.value('(Minutes/text())[1]', 'integer') End)
From
 @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
Where
 x.col.exist('(Provided[text()[1] = "true"])')=1 

  --method 2 
Select
 [On-site (Observations, Consultation)]
 , [Behavior Plan Meeting]
 , [Assessment/ Screening]
 , [Coaching]
 , [Telephone Consultation or Coaching]
 , [Case Management (Referrals, care coordination)]
 , [Documentation (Reports, Materials)]
 , [Parent Intake/ home or phone]
 , [Travel]
 ,[Training]
From ( 
 Select
  [Intervention] = x.col.value('(Intervention/text())[1]', 'varchar(100)')
  , [Minutes] = x.col.value('(Minutes/text())[1]', 'integer')
 From
  @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
 Where
  x.col.exist('(Provided[text()[1] = "true"])')=1) Y
Pivot (Sum([Minutes]) For [Intervention] In (
   [On-site (Observations, Consultation)]
   , [Behavior Plan Meeting]
   , [Assessment/ Screening]
   , [Coaching]
   , [Telephone Consultation or Coaching]
   , [Case Management (Referrals, care coordination)]
   , [Documentation (Reports, Materials)]
   , [Parent Intake/ home or phone]
   , [Travel]
   ,[Training]
 )
) as pvt

  I Have Nine Lives You Have One Only
  THINK!

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  March 13, 2019 at 10:02 am

  #2024342

  handkot - Wednesday, March 13, 2019 7:30 AM

  maybe this will help

  declare @xml as xml = '
<Data>
 <Interventions_x0020_Provided>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>On-site (Observations, Consultation)</Intervention>
   <Provided>true</Provided>
   <Minutes>35</Minutes>
   <Describe>Completed observation. Discussed with Janet (teacher) how Aisling''s behavior has been.</Describe>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Behavior Plan Meeting</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
   <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Assessment/ Screening</Intervention>
   </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Coaching</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
   <Interventions_x0020_Provided_x0020_SubTable>
    <Intervention>Telephone Consultation or Coaching</Intervention>
   </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Case Management (Referrals, care coordination)</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Documentation (Reports, Materials)</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Parent Intake/ home or phone</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Travel</Intervention>
   <Provided>true</Provided>
   <Minutes>45</Minutes>
  </Interventions_x0020_Provided_x0020_SubTable>
  <Interventions_x0020_Provided_x0020_SubTable>
   <Intervention>Training</Intervention>
  </Interventions_x0020_Provided_x0020_SubTable>
  </Interventions_x0020_Provided>
 </Data>'

  --just get the Intervention
Select
 x.col.value('(Intervention/text())[1]', 'varchar(100)')
 , x.col.value('(Minutes/text())[1]', 'integer')
 , x.col.value('(Intervention/text())[1]', 'varchar(100)')
From
 @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
--Where
-- x.col.exist('(Provided[text()[1] = "true"])')=1--method 1
Select
 Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'On-site (Observations, Consultation)' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Behavior Plan Meeting' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Assessment/ Screening' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Coaching' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Telephone Consultation or Coaching' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Case Management (Referrals, care coordination)' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Documentation (Reports, Materials)' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Parent Intake/ home or phone' Then x.col.value('(Minutes/text())[1]', 'integer') End)
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Travel' Then x.col.value('(Minutes/text())[1]', 'integer') End)     
 , Sum(Case When x.col.value('(Intervention/text())[1]', 'varchar(100)') = 'Training' Then x.col.value('(Minutes/text())[1]', 'integer') End)
From
 @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
Where
 x.col.exist('(Provided[text()[1] = "true"])')=1 

  --method 2 
Select
 [On-site (Observations, Consultation)]
 , [Behavior Plan Meeting]
 , [Assessment/ Screening]
 , [Coaching]
 , [Telephone Consultation or Coaching]
 , [Case Management (Referrals, care coordination)]
 , [Documentation (Reports, Materials)]
 , [Parent Intake/ home or phone]
 , [Travel]
 ,[Training]
From ( 
 Select
  [Intervention] = x.col.value('(Intervention/text())[1]', 'varchar(100)')
  , [Minutes] = x.col.value('(Minutes/text())[1]', 'integer')
 From
  @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
 Where
  x.col.exist('(Provided[text()[1] = "true"])')=1) Y
Pivot (Sum([Minutes]) For [Intervention] In (
   [On-site (Observations, Consultation)]
   , [Behavior Plan Meeting]
   , [Assessment/ Screening]
   , [Coaching]
   , [Telephone Consultation or Coaching]
   , [Case Management (Referrals, care coordination)]
   , [Documentation (Reports, Materials)]
   , [Parent Intake/ home or phone]
   , [Travel]
   ,[Training]
 )
) as pvt

  You're doing unnecessary reads of the XML tree.  You're reading each node twice, once to determine the Intervention and once to determine the minutes.  I think it will be faster if you only read it once.
  Select
     Sum(x.col.value('(.[Intervention="On-site (Observations, Consultation)"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Behavior Plan Meeting"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Assessment/ Screening"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Coaching"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Telephone Consultation or Coaching"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Case Management (Referrals, care coordination)"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Documentation (Reports, Materials)"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Parent Intake/ home or phone"]/Minutes/text())[1]', 'integer'))
,    Sum(x.col.value('(.[Intervention="Travel"]/Minutes/text())[1]', 'integer'))    
,    Sum(x.col.value('(.[Intervention="Training"]/Minutes/text())[1]', 'integer'))
From @xml.nodes('(/Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable)') x(col)
Where x.col.exist('(Provided[text()[1] = "true"])')=1


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  March 14, 2019 at 1:51 am

  #2024416

  handkot's method #2 is very efficient, the other code posted isn't.
  😎

  I would do this slightly differently for the sake of simplicity and code maintenance.

  
USE TEEST;
GO
SET NOCOUNT ON;

  DECLARE @TXML XML = '<Data>
<Interventions_x0020_Provided>
<Interventions_x0020_Provided_x0020_SubTable>
 <Intervention>On-site (Observations, Consultation)</Intervention>
 <Provided>true</Provided>
 <Minutes>35</Minutes>
 <Describe>Completed observation. Discussed with Janet (teacher) how Aisling''s behavior has been.</Describe>
</Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
<Intervention>Behavior Plan Meeting</Intervention>
</Interventions_x0020_Provided_x0020_SubTable>
 <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Assessment/ Screening</Intervention>
 </Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
<Intervention>Coaching</Intervention>
</Interventions_x0020_Provided_x0020_SubTable>
 <Interventions_x0020_Provided_x0020_SubTable>
  <Intervention>Telephone Consultation or Coaching</Intervention>
 </Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
<Intervention>Case Management (Referrals, care coordination)</Intervention>
</Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
 <Intervention>Documentation (Reports, Materials)</Intervention>
</Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
 <Intervention>Parent Intake/ home or phone</Intervention>
</Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
 <Intervention>Travel</Intervention>
 <Provided>true</Provided>
 <Minutes>45</Minutes>
</Interventions_x0020_Provided_x0020_SubTable>
<Interventions_x0020_Provided_x0020_SubTable>
 <Intervention>Training</Intervention>
</Interventions_x0020_Provided_x0020_SubTable>
</Interventions_x0020_Provided>
</Data>';

  ;WITH BASE_DATA AS
(
  SELECT
   IPS.DATA.value('(Intervention/text())[1]','VARCHAR(100)') AS Intervention
   ,IPS.DATA.value('(Minutes/text())[1]','INT') AS [Minutes]
  FROM @TXML.nodes('Data/Interventions_x0020_Provided/Interventions_x0020_Provided_x0020_SubTable') IPS(DATA)
  WHERE IPS.DATA.exist('(Provided[text()[1] = "true"])')=1
)
SELECT
  SUM(CASE WHEN BD.Intervention = 'On-site (Observations, Consultation)'     THEN BD.[Minutes] ELSE 0 END) AS [On-site (Observations, Consultation)]
 ,SUM(CASE WHEN BD.Intervention = 'Behavior Plan Meeting'          THEN BD.[Minutes] ELSE 0 END) AS [Behavior Plan Meeting]
 ,SUM(CASE WHEN BD.Intervention = 'Assessment/ Screening'          THEN BD.[Minutes] ELSE 0 END) AS [Assessment/ Screening]
 ,SUM(CASE WHEN BD.Intervention = 'Coaching'              THEN BD.[Minutes] ELSE 0 END) AS [Coaching]
 ,SUM(CASE WHEN BD.Intervention = 'Telephone Consultation or Coaching'      THEN BD.[Minutes] ELSE 0 END) AS [Telephone Consultation or Coaching]
 ,SUM(CASE WHEN BD.Intervention = 'Case Management (Referrals, care coordination)'  THEN BD.[Minutes] ELSE 0 END) AS [Case Management (Referrals, care coordination)]
 ,SUM(CASE WHEN BD.Intervention = 'Documentation (Reports, Materials)'      THEN BD.[Minutes] ELSE 0 END) AS [Documentation (Reports, Materials)]
 ,SUM(CASE WHEN BD.Intervention = 'Parent Intake/ home or phone'        THEN BD.[Minutes] ELSE 0 END) AS [Parent Intake/ home or phone]
 ,SUM(CASE WHEN BD.Intervention = 'Travel'               THEN BD.[Minutes] ELSE 0 END) AS [Travel]
 ,SUM(CASE WHEN BD.Intervention = 'Training'              THEN BD.[Minutes] ELSE 0 END) AS [Training]
FROM BASE_DATA BD;