CODEWITHSUNDEEP
windowspowershellloopsfor-loopforeach
caliGeek
caliGeek

Reputation: 419

Update XMl based off CSV values using Powershell

I have a CSV file with the values as below:

Age , Status

29 ,    0
41 ,    1
44,     1
27,     0
60,     1

The XML is as below:

<office>
  <staff branch="Torrance" Type="Implementation">
    <employee>
        <Name>Raj Parpani</Name>
        <function>Implementation</function>
        <age>29</age>
    </employee>
    <employee>
        <Name>Kevin Woo</Name>
        <function>Consultant</function>
        <age>41</age>
    </employee>
  </staff>
  <staff branch="Irvine" Type="Operations">
   <employee>
    <Name>David Woo</Name>
    <function>Data</function>
    <age>42</age>
   </employee>
   </staff>
 </office>

If XML age is equal to the age in CSV, I have to append the status attribute for that age from the csv to the employee. I have tried the code as below:

ORIGINAL CODE

$csv = Import-Csv 'C:\Users\rparpani\Desktop\test2.csv' | Select-Object "Age","Status"
$xml = New-Object XML
$xml.Load("C:\Users\rparpani\Desktop\test.xml")

$nodes = $xml.SelectNodes("/office/staff/employee")

Foreach($row in $csv)
{
    foreach($node in $nodes)
    {

    if($node.age -eq  $row.age)
    {
    $node.SetAttribute("status", $row.Status);
    }


    }

}

Can someone please suggest how to change this to do what I want it to do

MODIFIED CODE

    $csv = Import-Csv 'C:\Users\rparpani\Desktop\test2.csv' | Select-Object "Age","Status"
    $xml = New-Object XML
    $xml.Load("C:\Users\rparpani\Desktop\test.xml")

    $nodes = $xml.SelectNodes("/office/staff/employee")


    foreach($row in $csv) {
      foreach($node in $nodes) {
        if ($node.age -eq $row.age) {
          if ($node.status) {
            $node.status.innerText = $row.Status
          }
          else {
            $status = $xml.CreateNode("element", "status", "")
            $status.InnerText = $row.status
            $node.AppendChild($status)
          }
        }
      }
    }

    $xml.Save("C:\Users\rparpani\Desktop\test.xml")

Upvotes: 0

Views: 264

Answers (1)

Jawad
Jawad

Reputation: 11364

Following code does what you are looking for.

  1. You cannot use SetAttribute to create a new element with a value/InnerText. You need to create an element and append it to the node you are on. In your example xml, staff has two attributes, branch and Type that can be updated with SetAttribute method.
  2. If Status already exists, update it with new value.. otherwise create a new element.

CSV Content

Age,Status
29,0
41,1
44,1
27,0
60,1

Script to add attribute to Node `Employee

foreach($row in $csv) {
  foreach($node in $nodes) {
    if ($node.age -eq $row.age) {
      if ($row.status -eq "0") {
        $node.SetAttribute("status", "Hired")
      }
      else {
        $node.SetAttribute("status", "Fired")
      }
    }
  }
}

Upvotes: 1

Related Questions