[SOLVED] Change part of an attribute value

Issue

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?

Solution

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>

Answered By – mklement0

Answer Checked By – Candace Johnson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.