Transpose SSIS XML Elements from rows to columns in powershell

Question

We have a requirement to pull out the version information for all of our packages per project and cross reference that with the version information in SSISDB. I figured i would extract the .dtproj xml info I need into a table and then do a compare.

As a novice Powershell developer this is as far as I have got. It gives me the exact info I need but the data is per row as it appears in the xml file. I would like the data in a table format as follows

Name	versionMajor	VersionMinor	VersionBuild
PackageName1	1	0	5
PackageName2	1	0	21

here is the xml snippet


              
                {C55A0E46-B4E5-4CE5-8387-277E0472906F}
                SomePackage
                1
                0
                5
                
                
                {9A60744B-6F04-41BF-9F0C-A820BBD3FDCA}
                6
                
                
                0

this is my code

$namespace = @{SSIS = "www.microsoft.com/SqlServer/SSIS"}
$packageXpath = "//SSIS:PackageMetaData//SSIS:Property"
$packageNodes = Select-Xml -Path $path -XPath $packageXpath -Namespace $namespace | ForEach-Object{$_.Node} 
$packageNodes | Format-Table

Looking for the easiest way to do this. I have seen examples using regular expressions to transpose the data from a file and also examples where the element names (not the attributes) are the columns I need using @{Label}.

Thanks in advance.

user_15339544 · Answer

Here, try this:

[xml]$xml=@'

    
        {C55A0E46-B4E5-4CE5-8387-277E0472906F}
        SomePackage
        1
        0
        5
        
        {9A60744B-6F04-41BF-9F0C-A820BBD3FDCA}
        6
        
        0
    

'@

$props=$xml.PackageMetaData.Properties.Property

$hash=@{}

$props|%{
    $hash.Add($_.Name,$_.'#text')
}

[pscustomobject]$hash

Output should look like this:

Which is perfect for exporting to CSV / Xlsx

user_9898643 · Answer

Assuming

your XML indeed has the namespace declaration xmlns:SSIS="www.microsoft.com/SqlServer/SSIS"
it can have multiple SSIS:PackageMetaData elements

you could use below code to loop through and output what you want from it.
I'm using a Here-String for the xml content, but in real life you would read it from a file using

$xml = New-Object System.XML.XMLDocument
$xml.Load("full path of the XML file")

[xml]$xml = @'

    
        
            {C55A0E46-B4E5-4CE5-8387-277E0472906F}
            SomePackage
            1
            0
            5
            
            {9A60744B-6F04-41BF-9F0C-A820BBD3FDCA}
            6
            
            0
        
    
    
        
            {1234ABCD0E46-777-888-999-AAAABBBBCCCC}
            SomeOtherPackage
            1
            0
            21
            
            {9A60744B-6F04-41BF-9F0C-A820BBD3FDCA}
            6
            
            0
        
    

'@

$result = $xml.Packages.PackageMetaData| ForEach-Object {
    [PsCustomObject]@{
        Name         = ($_.Properties.Property | Where-Object { $_.Name -eq 'Name' }).'#text'
        VersionMajor = ($_.Properties.Property | Where-Object { $_.Name -eq 'VersionMajor' }).'#text'
        VersionMinor = ($_.Properties.Property | Where-Object { $_.Name -eq 'VersionMinor' }).'#text'
        VersionBuild = ($_.Properties.Property | Where-Object { $_.Name -eq 'VersionBuild' }).'#text'
    }
}

# output on screen
$result | Format-Table

# output to Csv file
$result | Export-Csv -Path 'path to the result.csv' -NoTypeInformation

Output on screen:

Name             VersionMajor VersionMinor VersionBuild
----             ------------ ------------ ------------
SomePackage      1            0            5           
SomeOtherPackage 1            0            21

Transpose SSIS XML Elements from rows to columns in powershell

2 Answers