Editing XML from CSV file

Hello. First time on this forum. I am new to PowerShell and even newer to XML I am trying to change XML values from a CVS file.

My CSV would look like:
HOST_NAME,HOST_IP,HOST_MAC,HOST_FQDN
ComputerName,92.1.1.09,12-23-34-45-56-67,ComputerName.FQDN.DOMAIN
ComputerName2,92.2.2.12,00-00-00-00-00-00,ComputerName2.FQDN.DOMAIN
ComputerName3,92.3.3.13,11-00-11-00-11-00,ComputerName3.FQDN.DOMAIN

I need to replace

$XML.CHECKLIST.ASSET

ROLE : None
ASSET_TYPE : Computing
HOST_NAME : Computer_Name12345
HOST_IP : 192.1.1.1
HOST_MAC :12-12-12-12-12-12
HOST_FQDN :Computer_Name12345.domain.end
TARGET_COMMENT : No Comment
TECH_AREA :
TARGET_KEY : Value0
WEB_OR_DATABASE : Value1
WEB_DB_SITE : Value2
WEB_DB_INSTANCE : Value3

I would need to save each row as its own XML file.
computerName1.xml

This is what I started with to make sure the values on the XML can be changed.

$filePathToTask = "C:\test\.XML"
$csvPath = "C:\test\COMPUTERS.CSV"
$xml = New-Object XML
$xml.Load($filePathToTask)
$element =  $xml.SelectSingleNode("//HOST_NAME")
$element.InnerText = "Computer_Name12345"
$element =  $xml.SelectSingleNode("//HOST_IP")
$element.InnerText = "Test_IP"
$element =  $xml.SelectSingleNode("//HOST_MAC")
$element.InnerText = "12-23-34-45-56-67"
$element =  $xml.SelectSingleNode("//HOST_FQDN")
$element.InnerText = "Computer_Name12345.domain.mil"
$xml.Save($filePathToTask)

and this is my sad work in progress:

$filePathToTask = "C:\test\TEST.XML"
$csv = Import-CSV "C:\test\COMPUTERS.CSV" | Select-Object HOST_NAME,HOST_IP,HOST_MAC,HOST_FQDN
$xml = New-Object XML
$xml.Load($filePathToTask)
$nodes = $xml.SelectNodes("/checklist/asset")
foreach($row in $csv) {

$element =  $xml.SelectSingleNode("//HOST_NAME")
$element.InnerText = "$e.HOST_NAME"
$element =  $xml.SelectSingleNode("//HOST_IP")
$element.InnerText = "$e.HOST_IP"
$element =  $xml.SelectSingleNode("//HOST_MAC")
$element.InnerText = "$e.HOST_MAC"
$element =  $xml.SelectSingleNode("//HOST_FQDN")
$element.InnerText = "$e.HOST_FQDN"
}
$xml.Save($filePathToTask)

I’m a little confused on your requirements. You said

but it looks like you are editing an existing XML file not creating one. Since you didn’t provide an example of an existing XML file(s), I’ll work off of creating new ones. If anything it could just overwrite the existing ones with all new data accomplishing the same thing.

The main thing you are missing in your script is a loop since each row in the csv gets its own XML file, you’ll need to iterate row by row. Also you can simplify creation of the nodes by creating an inner loop to iterate over each column in the row, creating a new node for each.

Here’s my take. I used your example for testing.

$testdata = @'
HOST_NAME,HOST_IP,HOST_MAC,HOST_FQDN
ComputerName,92.1.1.09,12-23-34-45-56-67,ComputerName.FQDN.DOMAIN
ComputerName2,92.2.2.12,00-00-00-00-00-00,ComputerName2.FQDN.DOMAIN
ComputerName3,92.3.3.13,11-00-11-00-11-00,ComputerName3.FQDN.DOMAIN
'@
$testfile = New-TemporaryFile
$testdata | Set-Content -Path $testfile.FullName

$xmlpath = "C:\Test"
$csv = Import-Csv -Path $testfile.FullName

foreach ($row in $csv)
{
    $xmlFile = New-Item -Path $xmlpath -Name "$($row.HOST_NAME).xml" -ItemType File -Force
    $xml = New-Object -TypeName XML
    $rootElement = $xml.CreateElement("HostInfo")

    $row.psobject.Properties.name |
        ForEach-Object {
            $element = $xml.CreateElement($_)
            $element.InnerText = "$($row.$_)"
            $rootElement.AppendChild($element) | Out-Null
        } #ForEach-Object script block (each column in row - inner loop)

    $xml.AppendChild($rootElement) | Out-Null    
    $xml.Save($xmlfile.FullName)    
} #foreach loop (csv row - outer loop)

Here is the output of one of the XML files

PS C:\test> Get-Content .\ComputerName.xml
<HostInfo>
  <HOST_NAME>ComputerName</HOST_NAME>
  <HOST_IP>92.1.1.09</HOST_IP>
  <HOST_MAC>12-23-34-45-56-67</HOST_MAC>
  <HOST_FQDN>ComputerName.FQDN.DOMAIN</HOST_FQDN>
</HostInfo>

PS C:\test> 
1 Like

I almost forgot one other thing is you can’t interpolate object properties like this in double-quote strings:

You’ll have to do it like this:

"$($e.HOST_FQDN)"

You can interpolate simple string variables without the subexpression, but once you use the property dereference operator “.” you’ll have to use it.

Thank you so much for you help @ralphmwr. It helps a lot. Below is my XML example. I’m just trying to edit the portion of file; the CSV would fill:

<HOST_NAME>Row B1</HOST_NAME>
<HOST_IP> Row B2</HOST_IP>
<HOST_MAC> Row B3</HOST_MAC>
<HOST_FQDN> Row B4</HOST_FQDN>

Then save to a new XML file. Which your scripts helps me do already. Which is great.

But I think I’m missing this part. Not sure what to replace “$xml.CreateElement(”“HostInfo”) with.

$rootElement = $xml.CreateElement(“HostInfo”)

type<?xml version="1.0" encoding="UTF-8"?>

<!--Viewer :: #.#-->

-<CHECKLIST>

-<ASSET>
<ROLE>None</ROLE>
<ASSET_TYPE>Computing</ASSET_TYPE>
<HOST_NAME>Computer_Name</HOST_NAME>
<HOST_IP> 123.123.123.123</HOST_IP>
<HOST_MAC> </HOST_MAC>
<HOST_FQDN> </HOST_FQDN>
<TARGET_COMMENT> </TARGET_COMMENT>
<TECH_AREA> </TECH_AREA>
<TARGET_KEY>Value</TARGET_KEY>
<WEB_OR_DATABASE>Value</WEB_OR_DATABASE>
<WEB_DB_SITE>DB</WEB_DB_SITE>
<WEB_DB_INSTANCE>MSSQLSERVER</WEB_DB_INSTANCE>

</ASSET>
+<STIGS>
</CHECKLIST>

This is how I am altering the script to run.

$xmlpath = "C:\test\"
$csv = Import-Csv -Path "C:\test\COMPUTERS.CSV"

foreach ($row in $csv)
{
    $xmlFile = New-Item -Path $xmlpath -Name "$($row.HOST_NAME).xml" -ItemType File -Force
    $xml = "C:\test\TEST.XML"
    $rootElement = $xml.CreateElement("HostInfo")

    $row.psobject.Properties.name |
        ForEach-Object {
            $element = $xml.SelectSingleNode($_)
            $element.InnerText = "$($row.$_)"
            $rootElement.AppendChild($element) | Out-Null
        } #ForEach-Object script block (each column in row - inner loop)

    $xml.AppendChild($rootElement) | Out-Null    
    $xml.Save($xmlfile.FullName)    
} #foreach loop (csv row - outer loop)
``

It creates the "ComputerName.XML" file in the directory but its empty.

Is there just one existing XML file with each host info inside tags or are there separate existing XML files for each host? If separate ones, what is the naming convention? Also, do these tags exist already in the existing file and you just need to modify the values or are you creating new tags that don’t exist in the existing XML file?

1/2. There is only one existing XML file and I would need to fill the each Host info from CSV (row) , then save that as computer1.xml
3. The tags already exist in the XML file and I would just need to modify the values.

Since you are working off an existing large XML with all the hosts in it and need to extract data by hosts, you wouldn’t copy the existing file to a new one. What you need to do is find the appropriate parent element containing the hostname each iteration (assuming the hostname is correct in the existing xml), then copy that parent node to a new xml object, make updates based on csv and then save. Here’s my take:

$testdata = @'
HOST_NAME,HOST_IP,HOST_MAC,HOST_FQDN
ComputerName,92.1.1.09,12-23-34-45-56-67,ComputerName.FQDN.DOMAIN
ComputerName2,92.2.2.12,00-00-00-00-00-00,ComputerName2.FQDN.DOMAIN
ComputerName3,92.3.3.13,11-00-11-00-11-00,ComputerName3.FQDN.DOMAIN
'@
$testfile = New-TemporaryFile
$testdata | Set-Content -Path $testfile.FullName

$existingxml = New-Object -TypeName XML
$existingxml.load("C:\Test\existing\ComputerName.xml")
$newxmlpath = "C:\Test"

$csv = Import-Csv -Path $testfile.FullName

foreach ($row in $csv)
{
    #find the host in existing xml and get it's parent
    $hostnode = $existingxml.GetElementsByTagName("HOST_NAME") |
        Where-Object {$_.'#text' -eq $row.HOST_NAME}

    $NewXML = New-Object XML
    $newnode = $NewXML.ImportNode(($hostnode.ParentNode.CloneNode($true)), $true)
    $Root = $NewXML.CreateElement("ASSET")
    $Root.AppendChild($newnode) | Out-Null
    $NewXML.AppendChild($Root) | Out-Null
    
    $row.psobject.Properties.name |
    ForEach-Object {
        $element = $NewXML.GetElementsByTagName($_)[0]
        $element.InnerText = "$($row.$_)"
    } #ForEach-Object script block (each column in row - inner loop)

    $NewXML.save((Join-Path -Path $newxmlpath -ChildPath "$($row.HOST_NAME).xml"))
  
} #foreach loop (csv row - outer loop)
1 Like