Count, Sort and Group-By in Powershell

Question

Are there any cool cmdlets that will help me do the following? I want something in Powershell that is as simple as doing the same in SQL:

select RootElementName , count(*) from Table
group by RootElementName 
order by RootElementName 

I'm all XML files in a directory, finding the root element of each XML file.

$DirectoryName = "d:\MyFolder\"
$AllFiles = Get-ChildItem $DirectoryName -Force 
foreach ($Filename in $AllFiles) 
{
     $FQFilename = $DirectoryName + $Filename
     [xml]$xmlDoc = Get-Content $FQFilename
     $rootElementName = $xmlDoc.SelectSingleNode("/*").Name; 
     Write-Host $FQFilename $rootElementName 
}

Desired Result:

RootName   Count 
--------   -----
Root1      15 
MyRoot     16 
SomeRoot   24 

I know I could could either create two arrays, or an array of objects, store the root elements in the array, and do the counts all using typical code, was just hoping that this new language might have something built-in that I haven't discovered yet.

Could I pipe the "Write-Host $FQFilename $rootElementName " to something that would behave something to the SQL I referred to above?

Solution

You can get groups and counts by using Group-Object like this:

$AllFiles | Group-Object RootElementName | Sort-Object Name | Select-Object Name, Count

In your current example, Write-Host doesn't write an object to the pipeline that we can sort or group. Write-Host only prints text to the screen to show the user something, ex. a script menu.

$DirectoryName = "d:\MyFolder\"
$AllFiles = Get-ChildItem $DirectoryName -Force | ForEach-Object {
    #The FullName-property contains the absolute path, so there's no need to join the filename and $directoryname
    [xml]$xmlDoc = Get-Content $_.FullName
    $rootElementName = $xmlDoc.SelectSingleNode("/*").Name

    #Outputing an object that we can group and sort
    New-Object -TypeName psobject -Property @{
        FileName = $_.FullName
        RootElementName = $rootElementName
    }
}
$grped = $AllFiles | Group-Object RootElementName | Sort-Object Name | Select-Object Name, Count

I'm creating an object with a FileName-property and the RootElementName so you have it if you need to retrieve the filename+rootelement for a list. If not, we could simplify this to:

$DirectoryName = "d:\MyFolder\"
$AllFiles = Get-ChildItem $DirectoryName -Force | ForEach-Object {
    #The FullName-property contains the absolute path, so there's no need to join the filename and $directoryname
    [xml]$xmlDoc = Get-Content $_.FullName

    #Output rootelementname
    $xmlDoc.SelectSingleNode("/*").Name
} 
$grped = $AllFiles | Group-Object | Sort-Object Name | Select-Object Name, Count