Change part of an attribute value

Question

I want to change a part of a connection string within an web.config file with Powershell.

I can read the desired connection string with the following code:

[xml]$XmlDoc = Get-Content C:\...\Web.config
$ConString = $XmlDoc.configuration.connectionStrings.add.connectionString[0]

and get the proper value extracted:

server=testDB01\sql14;uid=abc;pwd=def;database=ghi;language=english

Now I want to change the parts after the "=" to adapt the values as needed. I can access the values by spliting the text into part with e.g.

$ConString.Split(";")[0].Split("\")[1]

that returns

sql14

How can I now change that value and save it to the file?

Answer 1

Here's a self-contained example that uses the -replace operator to replace the database name in your connection string:

^{For an explanation of the regular expression used with -regex below, see https://regex101.com/r/Va3XN7/1 - note that whether that works depends on your browser's JavaScript engine; e.g., it works in Chrome, but may not work in Firefox. PowerShell uses .NET regular expressions, which, in this instance, behave the same as (modern) JavaScript's.}

Note:

• As Ansgar Wiechers points out in a comment, dot notation to drill into an XML document (e.g., $XmlDoc.configuration.connectionStrings.add.connectionString[0]) is convenient, but has limitations: it isn't suited to making structural changes to XML documents.

  • To perform structural changes, you must work directly with the methods of the underlying System.Xml.XmlNode[-derived] types.

• However, for simple, non-structural changes (which includes this case), dot notation can be used to make updates, namely:

  • Changing the text content of a leaf element.
  • Changing the value of an attribute (anywhere in the hierarchy) - as in this case.

• A general pitfall with dot notation is the potential for name collisions with the members of the underlying types - see this answer.

# Create a sample XML file.
@'
<?xml version="1.0"?>
<configuration>
    <connectionStrings>
        <add>
            <connectionString server="server=testDB01\sql14;uid=abc;pwd=def;database=ghi;language=english" />
            <connectionString server="..." />
        </add>
    </connectionStrings>
</configuration>
'@ > test.xml

# Read the file into an XML document.
[xml] $xmlDoc = Get-Content -Raw test.xml

# Update the 'server' attribute of the first <connectionString> element:
# Replace the existing db name with 'sql999'
$element = $xmlDoc.configuration.connectionStrings.add.connectionString[0]
$element.server = $element.server -replace '(?<=\bserver=.+?\\).+?(?=;)', 'sql999'

# Save the modified document back to the file.
# Note: This uses BOM-less UTF-8 encoding.
#       Convert-Path ensures that a full path is passed to the .Save() method,
#       because .NET's current directory typically differ from PowerShell's
$xmlDoc.Save((Convert-Path test.xml))

# Output the updated content:
Get-Content test.xml

The above yields the following - note how sql14 was replaced with sql999:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add>
      <connectionString server="server=testDB01\sql999;uid=abc;pwd=def;database=ghi;language=english" />
      <connectionString server="..." />
    </add>
  </connectionStrings>
</configuration>

Answer 2

You can use -Join to merge string back together:

$ConString = "server=testDB01\sql14;uid=abc;pwd=def;database=ghi;language=english"
$values1 = $ConString.Split(";")
$values = $values1[0].Split("\")

$values[1] = "foo"

$values = -Join $values, "\"
$values1[0] = $values

$ConString = -Join $values1,";"

Write-Host $ConString.Replace(" ", "")

Something along these lines