How to group and filter Sharepoint webservice XML response in Powershell

Question

I have a need to send automated emails based on Sharepoint list. I am able to retrieve the response and have code to send emails. But the email message body needs to be dynamic and need response date to be grouped. I am thinking of converting service response to represented as hashmap and use that hashmap to send targeted emails. Below is the sample of service response:

  <rs:data ItemCount="10">
     <z:row ows_Owner='1111@xxx.com' ows_Stories='Story1' ows_Program='Program1' />
     <z:row ows_Owner='1111@xxx.com' ows_Stories='Story2' ows_Program='Program1' />
     <z:row ows_Owner='1111@xxx.com' ows_Stories='Story3' ows_Program='Program2' />
     <z:row ows_Owner='1111@xxx.com' ows_Stories='Story4' ows_Program='Program2' />
     <z:row ows_Owner='2222@xxx.com' ows_Stories='Story5' ows_Program='Program1' />
     <z:row ows_Owner='2222@xxx.com' ows_Stories='Story6' ows_Program='Program1' />
     <z:row ows_Owner='2222@xxx.com' ows_Stories='Story7' ows_Program='Program1' />
     <z:row ows_Owner='2222@xxx.com' ows_Stories='Story8' ows_Program='Program2' />
     <z:row ows_Owner='2222@xxx.com' ows_Stories='Story9' ows_Program='Program2' />
     <z:row ows_Owner='2222@xxx.com' ows_Stories='Story10' ows_Program='Program2' />
  </rs:data>

What would be the best way to group and iterate the above data using powershell to send an email with body containing message with 'Stories' grouped by 'Program'. For ex: email sent to to '1111@xxx.com' will have message body as:

• Program1

  • Story1
  • Story2

• Program2

  • Story3
  • Story4

Update: Here is the pseudo code to better explain my requirement -

select unique $owners from rs:row

foreach $owner in $owners
{     $messageBody =""
    foreach $program in rs:row where ows_owner=$owner
    {
        $messageBody += $program "<br />
        foreach $story in rs:row where ows_owner=$owner and ows_program=$program
        $messageBody += $story "<br />
     }
         sendmail $owner, $messageBody 
}    

Just need code to easily achieve this in powershell especially filtering the data based on 'owner' and 'program' attributes

Answer 1

I would create custom PowerShell objects from the <row> nodes and then use Group-Object for grouping them by program:

[xml]$xml = Get-Content 'C:\path\to\input.xml'

$nsm = New-Object Xml.XmlNamespaceManager($xml.NameTable)
$nsm.AddNamespace('rs', 'http://...')  # replace with correct namespace URI

$xml.SelectSingleNode('//rs:data', $nsm) | select -Expand row | % {
  New-Object -Type PSCustomObject -Property @{
    Program = $_.ows_Program
    Story   = $_.ows_Stories
  }
} | group Program | select Name, @{n='Stories';e={$_.Group.Story}}

If grouping is not an option you could build a data structure of nested hashtables like this:

$data = @{}
$xml.SelectSingleNode('//rs:data', $nsm) | select -Expand row | % {
  New-Object -Type PSCustomObject -Property @{
    Owner   = $_.ows_Owner
    Program = $_.ows_Program
    Story   = $_.ows_Stories
  }
} | % {
  if (-not $data.Contains($_.Owner)) {
    $data[$_.Owner] = @{ $_.Program = @() }
  } elseif (-not $data[$_.Owner].Contains($_.Program)) {
    $data[$_.Owner][$_.Program] = @()
  }
  $data[$_.Owner][$_.Program] += $_.Story
}