Reading multiple XMLs with VBScript

Question

I've got the following piece of code in VBScript:

Dim OrganizationInfo, name
Dim Location, country

Set xmlDoc = CreateObject("Microsoft.XMLDOM") 
xmlDoc.Async = "False"
xmlDoc.setProperty "SelectionLanguage", "XPath"
xmlDoc.load("C:\Users\AdminUser\XMLArchive.xml")

Set fso = CreateObject("Scripting.FileSystemObject")
Const ForAppending = 8
Set WriterObject = fso.OpenTextFile("C:\Users\AdminUser\Folder\TEXTFile.txt", ForAppending, True) 


For Each OrganizationInfo In xmlDoc.SelectNodes("//OrganizationInfo/OrganizationName")
    name = OrganizationInfo.Text

    For Each Location In OrganizationInfo.SelectNodes("//Location")
        COUNTRY = Location.Text
        WriterObject.WriteLine DATA & ";" & Pais
    Next
Next

I'm using Xpaths to write lots of XML Node's Text in multiple txts, without problems.

Now I need to repeat this process with 1000 XMLs in the same directory that "XMLArchive.xml" is inserted, without knowing their names... Can anyone please help? I've seen similar cases here, but none with the same intention.

score 0 · Accepted Answer · answered Dec 03 '13 at 20:02

When in doubt, read the documentation. Files in a directory can be processed like this:

Set fso = CreateObject("Scripting.FileSystemObject")

Set xml = CreateObject("Msxml2.DOMDocument.6.0") 
xml.Async = False
xml.setProperty "SelectionLanguage", "XPath"

For Each f In fso.GetFolder("C:\Users\AdminUser").Files
  If LCase(fso.GetExtensionName(f)) = "xml" Then
    xml.Load f.Path

    If xml.ParseError = 0 Then
      'your XML processing code goes here
    Else
      WScript.Echo "Error parsing '" & f.Path & "': " & xml.ParseError.Reason
    End If
  End If
Next

score 0 · Answer 2 · edited May 23 '17 at 12:13

Here's a Sub that will find the names of all of the XML files in a folder that you designate.

Also see this answer for instructions on downloading a complete VBScript Reference as a Windows help file.

Sub ProcessXmlFiles(sFolderPath)
    Dim oFso, oFolder
    Dim sFilePath

    Set oFso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFso.GetFolder(sFolderPath)
    For Each sFilePath In oFolder.Files
        If IsXmlFile(sFilePath) Then
            WScript.Echo sFilePath
            ' ProcessXmlFile(sFilePath)  ' Do your XML file processing here!
        End If
    Next

    Set oFolder = Nothing
    Set oFso = Nothing
End Sub

Function IsXmlFile(sFilePath)
    Const REGEXPR = "\.xml$"
    Dim oRegex, oMatches

    Set oRegex = New RegExp
    oRegex.Pattern = REGEXPR
    oRegex.IgnoreCase = True
    oRegex.Global = False
    Set oMatches = oRegex.Execute(sFilePath)

    IsXmlFile = (oMatches.Count > 0)
    Set oMatches = Nothing
    Set oRegex = Nothing
End Function

Regular expressions are total overkill for determining a file's extension. Particularly when you're already using a `FileSystemObject` instance. — Ansgar Wiechers, Dec 04 '13 at 10:06
@Ansgar - Yes, you are absolutely right. As you indicate in your answer, `GetExtensionName()` is more appropriate for this situation. (And that was an oversight on my part.) However, a regex approach could be useful in situations where more sophisticated pattern matching is required for locating the files of interest. — DavidRR, Dec 04 '13 at 13:08

Reading multiple XMLs with VBScript

2 Answers2