I am looking at creating a vb script to replace a value in multiple xml files quickly.

I am looking at creating a vb script to replace a value in multiple xml files quickly.  It is a single xml file called dbid.xml and it is in each project folder. There are thousands of projects under different file folders under one main directory.  Also would like to create a log of change to help validate change.

I have all the location in a variable file so just need code to run.

I need to replace the value in the parameters <PHYSICAL_DIRECTORY> </PHYSICAL_DIRECTORY>

Example
From
<PHYSICAL_DIRECTORY>\\server21\qc\Test_Target_Project1\</PHYSICAL_DIRECTORY>
To
<PHYSICAL_DIRECTORY>\\server311\qc11\Test_Target_Project1\</PHYSICAL_DIRECTORY>

Sample dbid.xml file

<?xml version="1.0" encoding="UTF-8"?>
<ProjectDescription>
      <PROJECT_NAME>Test_Target_Project1</PROJECT_NAME>
      <DB_TYPE>2</DB_TYPE>
      <DESCRIPTION>Created on 2013-08-02 09:36:37</DESCRIPTION>
      <DB_CONNSTR_FORMAT>jdbc:mercury:sqlserver://testqlserver:1433</DB_CONNSTR_FORMAT>
      <DB_NATIVE_AUTHENTICATION>Y</DB_NATIVE_AUTHENTICATION>
      <DB_NAME>project1_db</DB_NAME>
      <DBSERVER_NAME>testdbserver</DBSERVER_NAME>
      <DB_USER_PASS>qwertyuio</DB_USER_PASS>
      <PR_HAS_VCSDB>N</PR_HAS_VCSDB>
      <PHYSICAL_DIRECTORY>\\server21\qc\Test_Target_Project1\</PHYSICAL_DIRECTORY>
      <USERS_QUOTA>-1</USERS_QUOTA>
      <PR_IS_ACTIVE>Y</PR_IS_ACTIVE>
      <SAQ_IS_ACTIVE>N</SAQ_IS_ACTIVE>
      <VM_REPOSITORY></VM_REPOSITORY>
      <PR_LANGUAGE>English</PR_LANGUAGE>
      <PROJECT_TYPE>Standard</PROJECT_TYPE>
      <IS_TEMPLATE>N</IS_TEMPLATE>
      <PROJECT_UID>lkjhgfdsa11234</PROJECT_UID>
      <PR_SMART_REPOSITORY_ENABLED>Y</PR_SMART_REPOSITORY_ENABLED>
      <PR_IS_QPM_AUTO_CALC_ENABLED>Y</PR_IS_QPM_AUTO_CALC_ENABLED>
</ProjectDescription>
williamflAsked:
Who is Participating?
Advertise Here
 
Bill PrewConnect With a Mentor Commented:
Give this a test, after saving as a VBS and adjusting the strings near the top as needed.  It will search the specified base folder for all files with the specified name, and replace the existing file path on the element you described with the one you specify.  Since your XML file was pretty simple I just processed it as a text file, which should work fine for you.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define the folder to scan, and the age of files to locate
strBaseDir = "B:\EE\EE28395531\Files"
strFindFile = "dbid.xml"
strReplaceString = "\\server311\qc11\Test_Target_Project1\"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Look for files
FindFiles objFSO.GetFolder(strBaseDir)

' Subroutine (recursive) to search for files to delete
Sub FindFiles(objFolder)
   On Error Resume Next

   ' Look at all files in this folder
   For Each objFile In objFolder.Files
      ' Make sure it's the filename
      If LCase(objFile.Name) = LCase(strFindFile) Then
         UpdateFile objFile.Path
      End If
   Next

   ' See if we were able to access this folder, if not don't recurse into it
   If Err.Number = 0 Then
       ' Recursively drill down into subfolder
       For Each objSubFolder In objFolder.SubFolders
           FindFiles objSubFolder, intLevel
       Next
   End If

End Sub

' Subroutine to update a file
Sub UpdateFile(strFile)
   ' Read entire input file into a variable and close it
   Set objFile = objFSO.OpenTextFile(strFile, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   objFile.Close
   Set objFile = Nothing

   ' Replace desired strings
   Set objRegExp = New RegExp
   objRegExp.Global = True
   objRegExp.IgnoreCase = False
   objRegExp.Pattern = "(\<PHYSICAL_DIRECTORY\>).*(\</PHYSICAL_DIRECTORY\>)"
   strData = objRegExp.Replace(strData, "$1" & strReplaceString & "$2")

   ' Write file with any changes made
   Set objFile = objFSO.OpenTextFile(strFile, ForWriting, True)
   objFile.Write(strData)
   objFile.Close
   Set objFile = Nothing
End Sub

Open in new window

~bp
0
 
williamflAuthor Commented:
It worked great,  just need to get it to loop and read the parameters from a text file now for strBaseDir and the strReplaceString. This way we can run updates in a batch mode. I have done loops before just need to find code used before.
0
 
Bill PrewCommented:
Okay, glad that worked for you, let me know if you have any further questions.

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.