Solved

# PowerShell and XML editing

Posted on 2013-06-21
114 Views
I have the following code that allows me to change a line in web config but if there are 2 connection strings it fails.

How can I change it so it works?

$New_Pass = 'Welcome"$Web = 'C:\inetpub\wwwroot\Contoso\web.config'
$CONNECTION_STRING ="Data Source=JOHN.WORLD;User Id=peter;password=" +$New_Pass

$doc = new-object System.Xml.XmlDocument$doc.Load($Web)$root = $doc.get_DocumentElement(); # Change password # If there 2 entries it will fail$root.connectionStrings.add.connectionString = $CONNECTION_STRING$doc.Save($Web) Code does exactly what I need but if there are 2 or more entries in that tag it will fail 0 Question by:hwalch • 11 • 6 • 2 24 Comments LVL 69 Expert Comment ID: 39266187 Would you mind posting an example web.config? 0 Author Comment ID: 39267201 <?xml version="1.0"?> <configuration> <connectionStrings> <add name="ConStringPSupply" connectionString="Data Source=.;Initial Catalog=pSupply;User ID=sa;Password=1234"/> <add name="ConString1" connectionString="Data Source=.;Initial Catalog=NEW1;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <httpHandlers> <remove verb="*" path="*.asmx"/> <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/> <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/> <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/> <add verb="GET" path="CrystalImageHandler.aspx" type="CrystalDecisions.Web.CrystalImageHandler, CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> </httpHandlers> <httpModules> <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/> </httpModules> <compilation debug="true"> <assemblies> <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> <add assembly="System.Web.Extensions.Design, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> <add assembly="System.Web.RegularExpressions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> <add assembly="System.Drawing.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> <add assembly="CrystalDecisions.CrystalReports.Engine, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="CrystalDecisions.ReportSource, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="CrystalDecisions.Shared, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="CrystalDecisions.Web, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="CrystalDecisions.ReportAppServer.ClientDoc, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="CrystalDecisions.Enterprise.Framework, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="CrystalDecisions.Enterprise.InfoStore, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/> <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> <!--<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>--> </assemblies> </compilation> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Windows"/> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors> --> </system.web> </configuration>  0 LVL 80 Expert Comment ID: 39268657 <# <connectionStrings> <add name="ConStringPSupply" connectionString="Data Source=.;Initial Catalog=pSupply;User ID=sa;Password=1234"/> <add name="ConString1" connectionString="Data Source=.;Initial Catalog=NEW1;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings> #>$path = "D:\Documents\WindowsPowerShell\Scripts\"
$Web =$path + "ee22jun13.xml"
#$output = "connectionstring2.xml"$New_Pass = "Welcome"
$Connection_String1 ="Data Source=JOHN.WORLD;User Id=peter;password=" +$New_Pass
$Connection_String2 = "Data Source=WAYNE.WORLD;User ID=peter;password=" +$New_Pass
$xml = New-Object XML$xml.Load($web) # Change password # If there 2 entries it will fail$xml.configuration.connectionStrings.Add[0] = $Connection_String1$xml.configuration.connectionStrings.Add[1] = $Connection_String2$xml.Save($Web) cls$Connection_String1
Get-Content $web  It changes it in memory but for the life of me I can't get it to save 0 Author Comment ID: 39268863 Interesting, it looks very simple. I will test it on Monday. Now I web.config could have 2 or 3 or more connectionstrings how do I find out how many are there? Thanks 0 LVL 80 Expert Comment ID: 39268874$xml.configuration.connectionStrings.add.Count
Will return the number of connectionstrings objects
remember to start your counter when assigning from 0 and not 1 i.e. two objects  first is object[0]
0

Author Comment

ID: 39272084
You were right.  I am able to change the value perfectly.  I was able to see the count also, it returns two.  But when I save the changes, nothing happens.

I tried to do one save and then do the second and then save but it didn't change the outcome.

Beatiful !!! :-)
0

LVL 69

Expert Comment

ID: 39272191
Wrong. Neither in memory nor on disk you will see any change - because the change is wrong ;-).
When changing the connection string, we need to provide the attribute "connectionString", as was in the original code.
$path = "c:\temp\ee\tst\"$Web = $path + "ee22jun13.xml"$New_Pass = "Welcome"
$Connection_String1 ="Data Source=JOHN.WORLD;User Id=peter;password=" +$New_Pass
$Connection_String2 = "Data Source=WAYNE.WORLD;User ID=peter;password=" +$New_Pass
$xml = New-Object XML$xml.Load($web) # Change password$xml.configuration.connectionStrings.Add[0].connectionString = $Connection_String1$xml.configuration.connectionStrings.Add[1].connectionString = $Connection_String2$xml.Save($Web) cls Get-Content$web

Of course that only works well if you have exactly the same amount and content of connectionstrings everywhere.
0

LVL 69

Expert Comment

ID: 39272255
This seems to be much better:
$path = "c:\temp\ee\tst\"$Web = $path + "ee22jun13.xml"$New_Pass = "Welcome"

$xml = New-Object XML$xml.Load($web) # Change password$xml.configuration.connectionStrings.add |
? { $_.ConnectionString -like '*Password=*' } | % {$_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$New_Pass" }
$xml.Save($Web)
cls
Get-Content $web  0 Author Comment ID: 40115287 Sorry for going away for so long and thank you for your input. That last entry almost works. It's the only one I have managed to make work. However this is assuming that all paswords are the same. That is not the case. If you have 3 connections strings, one will be forWindows SQL, one for Oracle, and one for Sybase or just 3 different servers. So you need to be able to replace only one for each add name 0 LVL 69 Expert Comment ID: 40115357 In that case we just need to check for the fitting Name attribute: $path = "c:\temp\ee\tst\"
$Web =$path + "web.config"

$New_Pass = "Welcome"$xml = New-Object XML
$xml.Load($web)
$xml.configuration.connectionStrings.add | ? {$_.Name -eq 'ConString2' } |
? { $_.ConnectionString -like '*Password=*' } | % {$_.ConnectionString = $_.ConnectionString -replace 'Password=(\w*)', "Password=$New_Pass" }
$xml.Save($Web)
cls
Get-Content $web  Line 11 isn't really necessary, as we are very specific in which connection string we want to change, and the replace won't do any harm if there is no password tag (as with trusted security). You can remove it or leave it as-is hence. 0 Author Comment ID: 40406878 NOOOOOOOOOOOOOOOOOOOOOOOOOOOO I am actively using it 0 Author Comment ID: 40406886 That is totally incorrect. The posted script works only for one line. That was not the question. Do I need to repost my question? 0 Author Comment ID: 40406888 I think if you are going to assign points for me, 500 is incorrect if the question is only partially answered. 500 would be for a perfect answer. 0 Author Comment ID: 40406893 So is there any one else who want to help? There is a lot activity about to change Web.config in google weird that no-one has the knowledge. 0 Author Comment ID: 40406933 I did 0 Author Comment ID: 40406935 Do do I need to start all over again and repost? 0 LVL 69 Expert Comment ID: 40406946 You haven't been verbose and precise about your requirements. From what you have posted now, I derive you need to change all db connect strings, and have to do so with different passwords? How should that happen? Check for how many connection strings are in there, then ask a new password for each? Is the sequence the same all the time, or might they get mixed up, or whatsoever? We either need a static sequence of strings, with. e.g. ConString2 always being the MSSQL connections, or something else clearly identifying each connection string and the new password to associate. We can also do a simple old => new password replacement, if that is more simple. 0 Author Comment ID: 40407400 The logic is correct but the code to navidate from ConnectionString to connectionString and replace the Password with Xpath that is what I need 0 LVL 69 Accepted Solution Qlemo earned 500 total points ID: 40408551 cls$path = "c:\temp\ee\"
$Web =$path + "web.config"

$xml = New-Object XML$xml.Load($web) # Change password$xml.configuration.connectionStrings.add |
% {
$pwd = switch ($_.Name)
{
'ConString1'       { 'NewPass1' }
'ConstringPSupply' { 'NewPass2' }
}
$_.ConnectionString =$_.ConnectionString -replace 'Password=(\w*)', "Password=$pwd" }$xml.Save($Web) Get-Content$web

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

You may have a outside contractor who comes in once a week or seasonal to do some work in your office but you only want to give him access to the programs and files he needs and keep privet all other documents and programs, can you do this on a loca…
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…