Iterate through xml elements with powershell

Question

I have some xml files that have different elements. I would like to write some code either in c# or PowerShell that iterates through each element and gets the value. I would like it to be generic enough so that I can use it with other xml files. All xml files have the same format but the names of the elements differ.

Thanks a lot for your help.

<root>
  <list>
       <FirstName>Abc</FirstName>
       <LastName>LT</LastName>
       <Occupatoin>Eng</Occupation>
       <BirthDate></BirthDate>
      ...
 </list>
</root>

----------------------
XML file 2

<root>
  <Trainings>
       <Java>Ab</Java>
       <NET>b</NET>
       <SQL>c</SQL>
       <Powershell>d</Powershell>
      ...
 </Trainings>
</root>

---

Another xml has been introduced that will be the base for the iteration of elements in the above xml files.

get the values of the elements above based on the attributes of each element below:

<root>
    <Element Name="Firstname />
    <Element Name="Lastname" />
    <Element Name="Occupation" />
    <Element Name="Java" />
    <Element Name="Net" />
...
</root>


[System.Xml.XmlDocument] $xdLists = new-object System.Xml.XmlDocument
[System.Xml.XmlDocument] $xdMig = new-object System.Xml.XmlDocument
$listfile = "C:\PowershellFiles\XmlLists.xml"
$xdLists.load($listfile)

$xdNodes= $xdLists.selectnodes("//DestinationLists/DestinationList")
$migCols = $xdLists.selectnodes("//MigrationColumns/Columns/Column")

#LOOP 1-----------------------------------------
foreach($xnode in $xdNodes)
{
    Write-Host $xnode.Attributes.GetNamedItem("MigrationFile").Value
    $destLists = $xnode.Attributes.GetNamedItem("Name").Value
    $migfiles = $xnode.Attributes.GetNamedItem("MigrationFile").Value
    
    
    Write-Host $destLists
    
    #Check if the xml file to read from exists
    
    if($migFiles -ne $null)
    {
            $xdMig.Load($migfiles)
        
            $spSite = Get-SPSite "http://sp2010:100" 
            $spWeb = $spSite.OpenWeb()

            $list = $spWeb.Lists[$destLists]

            foreach($nCol in $migCols)
            {
                $destListCol =  $nCol.Attributes.GetNamedItem("DestList").Value
                $sourcCol =  $nCol.Attributes.GetNamedItem("SourceCol").Value

#               Write-Host $col " - " $list.Title

                if($destListCol -eq $list.Title)
                {
                    Write-Host $destListCol " - " $list.Title " - Source Column: " $sourcCol  -ForegroundColor Green
                    Write-Host
                    
                    # ----------------------- time to search the exported lists --------------------
                    Write-Host "Search the exported list for the right column" -ForegroundColor  DarkYellow
                    
                    if($xdMig.DocumentElement -ne $null)
                        {
                            $xnList = $xdMig.DocumentElement.ChildNodes

                    #           LOOP 2----------------------------------------
                            Write-Host $xnList.Count " items found" -ForegroundColor Red
                            foreach($xn in $xnList)
                            {
                                Write-Host $xn.Name -ForegroundColor Red
                                
                                $nList = $xdMig.SelectNodes("//"+$xn.Name)
                                $lItem = $list.Items.Add()

                                foreach($n in $migCols)
                                  {
                                  
                                    if($n.Attributes -ne $null)
                                    {
                                        $sourceCol = $n.Attributes.GetNamedItem("SourceCol").Value
                                        $destCol = $n.Attributes.GetNamedItem("DestCol").Value
                                        $destList = $n.Attributes.GetNamedItem("DestList").Value
                                        
                                        Write-Host "Dest Col: " $destCol  "-  Sour Col: " $xn[$sourceCol].Name 
                                        Write-Host $destList -ForegroundColor Red

                                        if($list.Title -eq $destList)
                                        {
                                            if($xn[$sourceCol] -ne $null )
                                            {
                                                if($list.Fields[$destCol] -ne $null)
                                                {
                                                    $lItem[$destCol] = $xn[$sourceCol].InnerText    
                                                }
                                                        
                                            }else
                                            {
                                                Write-Host   $sourceCol " was not matched" -ForegroundColor Yellow
                                            }
                                        }
                                     }
                                  }
                                  $lItem.Update()
                                  Write-Host "-----------------------------------"
                            }

                        }
                }
            }
    }
}

Answer 1

I ran into a similar requirement where I needed to delve into varying XML structures. This recursive routine works well for most xml I have run into.

function GetNodes($root,$j){
    $nodes=$root.ChildNodes
    $j++
    Foreach($child in $nodes){
       $tabs=""
       for ($i = 1; $i -lt $j; $i++){ $tabs+="`t"  }
       if ($child.NodeType -eq "Element"){
            $value=$root.($child.Name)
            if ($value.GetType().Name -eq "XmlElement"){
                write-host "$tabs$($child.Name)" -ForegroundColor green
            }else{
                write-host "$tabs$($child.Name): $($value)"
            }  
        }
        if ($child.HasChildNodes){ GetNodes $child $j }
    }
    $j--
}

[xml]$xml=get-content C:\Temp\test.xml
GetNodes $xml

The output for file 1 looks like this

root
    list
        FirstName: Abc
        LastName: LT
        Occupation: Eng
        BirthDate: 

I combined files 1 and 2 to get this output:

root
    list
        FirstName: Abc
        LastName: LT
        Occupation: Eng
        BirthDate:
    Trainings
        Java: Ab
        NET: b
        SQL: c
        Powershell: d

Answer 2

You could combine powershell with xpath. Assume that you have fixed XML errors and renamed Element.Name to be in same case as in data file:

your_file.xml fixed content:

<root> 
  <list> 
       <FirstName>Abc</FirstName> 
       <LastName>LT</LastName> 
       <Occupation>Eng</Occupation> 
       <BirthDate></BirthDate> 
      ... 
 </list> 
</root>

index_file.xml fixed content:

<root> 
    <Element Name="FirstName" /> 
    <Element Name="LastName" /> 
    <Element Name="Occupation" /> 
    <Element Name="Java" /> 
    <Element Name="NET" /> 
... 
</root>

PoSH to map'em:

$xmlIndex = [xml](gc ".\index_file.xml")
$xml = [xml](gc ".\your_file.xml")
$allValues = @{}; 

$xmlIndex.SelectNodes("//Element/@Name") | 
    %{ $nodeName = $_."#text"; $xml.SelectNodes("//$nodeName/text()") } |
    % { $allvalues[$_.ParentNode.ToString()] = $_.Value };

$allValues | ft

output will be followed

Name                           Value
----                           -----
Occupation                     Eng
FirstName                      Abc
LastName                       LT

and

Name                           Value
----                           -----
NET                            b
Java                           Ab