Parse XML Data

  • jbalbo

    SSCertifiable

    Points: 7237

    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

    SSCarpal Tunnel

    Points: 4501

    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: 76493

    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: 182344

    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;

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply