Replace content of an XML based on a lookup table in powershell

Question

I have an XML file with some specific keywords I need to replace, here's an example:

    <?xml version="1.0" encoding="utf-8"?>
<!--Exported at 23-09-2019 10:01:23-->
<DEFTABLE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Folder.xsd">
    <SMART_FOLDER JOBISN="1" APPLICATION="6261" SUB_APPLICATION="R001J_CMNOK" JOBNAME="6261_R001J_CMNOK" DESCRIPTION="Snapshot des jobs not ok de control-m ds intraprd2" >
        <JOB JOBISN="2" APPLICATION="6261" SUB_APPLICATION="R001J_CMNOK" MEMNAME="R010J.BAT" JOBNAME="6261R001J010J_CMNOK" DESCRIPTION="[07:00] Chargement job ended_notok" CREATED_BY="Graf">
            <VARIABLE NAME="%%LIBMEMSYM" VALUE="%%VG_LIBMEMSYM./CTM.var" />
            <RULE_BASED_CALENDARS NAME="*" />
        </JOB>
        </SMART_FOLDER>
</DEFTABLE>

I need to replace "Recette" with "Production" and "6261_R001J_CMNOK" with "6261_P001J_CMNOK" and write the new XML in a copy of the file I made earlier like this:

$CreateFileName = $($OriginalXMLFilePath -replace ".{4}$") + "_" + $(Get-Date -UFormat "%Y-%m-%d_%H%M%S") + ".xml"
$CopyFile = Copy-Item $OriginalXMLFilePath -Destination $CreateFileName -Force
# New file name is 6261_XML_2019-11-18_135100.xml

I made the following variables:

$A_Lookup = @("Recette","6261_R001J_CMNOK")
$B_Replace = @("Production", "6261_P001J_CMNOK")

Here was the rest of my code to replace the XML:

$Read = Get-Content -Path $OriginalXMLFilePath
for ($i = 0; $i -lt $A_Lookup.length; $i++) {
    $Read = $Read -replace $A_Lookup[$i], $B_Replace[$i]
}
Remove-Item -Path $OriginalXMLFilePath -Force 
Add-Content -Path $OriginalXMLFilePath -Value $Read -Force

Problem is, there was around 25+ items after the @( and that's not very optimized when we need to add more content there. So I decided to make a lookup table instead:

$lookupTable = @{
    "Recette" = "Production"
    "6261_R001J_CMNOK" = "6261_P001J_CMNOK"
}

But now my main issue is that the part of my code that would replace $A_Lookup by $B_Replace doesn't work anymore. I tried the "Solution 4" on this page and other solutions around Stack Overflow but none really worked for me. What's blocking me the most is that I can't seem to write anything into the new XML file.

I'm running on the latest version of PowerShell.

Do not use string replacements for operations like that. Read the file into an XML object and use XPath expressions for selecting the attribute whose value you want to replace. — Ansgar Wiechers, Nov 18 '19 at 13:30
Generally, I would **not** use text replacements on an XML file but tread an `XML` document as an `XML` document, see e.g. [How to change the value of XML Element attribute using PowerShell?](https://stackoverflow.com/q/24679454/1701026), or more common: [Calling XMLDocument Methods in PowerShell](https://devblogs.microsoft.com/scripting/calling-xmldocument-methods-in-powershell/) — iRon, Nov 18 '19 at 13:33
@AnsgarWiechers thank you for your answer, i'm a beginner so i have to idea how to do that. Is that what you meant ? https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-xml?view=powershell-6 — Yokiie, Nov 18 '19 at 13:35
`Select-Xml` is best suited for reading data from XML, but you want to modify nodes, so you should use the XML objects' `SelectNodes()` method (see the first link iRon posted, or do a search for [xml] and selectnodes here on SO). — Ansgar Wiechers, Nov 18 '19 at 13:38
So my issue with the SelectNodes() method is that, i can have many nodes i need to replace with the same text (ex. : APPLICATION="RECETTE" and DATACENTER="RECETTE", etc) so that's why i used the string replacement method, so i don't have to write every single node in my huge XML — Yokiie, Nov 18 '19 at 13:57
Basic algorithm: loop over the keys of your hashtable, select the nodes matching the key, loop over the selected nodes, replace the key with the corresponding value. — Ansgar Wiechers, Nov 19 '19 at 09:02

score 0 · Answer 1 · answered Nov 18 '19 at 14:13

You can break down your task as follows:

Discover all XML nodes with attributes
For each such node:
- Inspect the value of each attribute
- If the current value exists as a key in your lookup table, replace it

$LookupTable = @{
  "Recette" = "Production"
  "6261_R001J_CMNOK" = "6261_P001J_CMNOK"
}

$Document = [xml](Get-Content -Path $OriginalXMLFilePath)

# Let's find all nodes that have any attributes
$NodeList = $Document |Select-Xml -XPath '//*[@*]'

# iterate over the nodes, and replace all relevant attribute values
foreach($Node in $NodeList.Node) {
  foreach($Attribute in $Node.Attributes) {
    if($LookupTable.ContainsKey($Attribute.Value)){
      $Attribute.Value = $LookupTable[$Attribute.Value]
    }
  }
}

# save the document
$CreateFileName = $($OriginalXMLFilePath -replace ".{4}$") + "_" + $(Get-Date -UFormat "%Y-%m-%d_%H%M%S") + ".xml"
$Document.Save((Resolve-Path $CreateFileName).ProviderPath)

Replace content of an XML based on a lookup table in powershell

1 Answers1