parsing XML data into a TXT file

Question

I have a TXT and an XML that look like this:

temp-4.txt

1907|
233845|
4187|
4668|
5471|

VendorConfig.xml

<?xml version="1.0" encoding="utf-8" ?>
<Vendors>
  <Vendor>
    <SourceID>210986|</SourceID>
    <SourceName>Rise Commercial Services|SPRING-330RAYFO</SourceName>
    <DestName>Rise Commercial Services|SPRING-330RAYFO</DestName>
  </Vendor>
  <Vendor>
    <SourceID>1907|</SourceID>
    <SourceName>Franklin Machine Products Inc|LUMBER-10MTHOL</SourceName>
    <DestName>Franklin Machine Products Inc1907|</DestName>
  </Vendor>
  <Vendor>
    <SourceID>233845|</SourceID>
    <SourceName>Coastline Electric Co Inc233845|</SourceName>
    <DestName>Coastline Electric Co Inc233845|</DestName>
  </Vendor>
</Vendors>

I need to loop through each line item in the TXT file and check to see if it exists in the VendorConfig.xml using a VBScript. If it exists in the XML as a SourceID, I need to append additional information from the XML. If it does not exist in the XML as a SourceID, I need to append an error message. What I have so far looks like this, but the XML parser does not appear to be returning any matches, and I can't figure out why:

THE VBSCRIPT

Set objXMLDoc = CreateObject("Microsoft.XMLDOM")
objXMLDoc.async = False
objXMLDoc.load("C:\myAbsolutePath\VendorConfig.xml")
If objXMLDoc.parseError.errorCode <> 0 Then
    MsgBox ("XML connection error detected.")
End If

Set Root = objXMLDoc.documentElement
Set NodeList = Root.getElementsByTagName("Vendor")

Dim AddVendorDetails, InputFile, FSO, oFile, strTempSourceIDs
InputFile = "C:\myAbsolutePath\temp-4.txt"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFile = FSO.OpenTextFile(InputFile)
strTempSourceIDs = oFile.ReadAll
oFile.Close

Dim splitCounter
Dim splitCatcher
Dim IDtest

SplitCatcher = Split(strTempSourceIDs, vbCrLf)
For splitCounter = 0 To UBound(SplitCatcher)
    IDtest = SplitCatcher(splitCounter)
    For Each i In NodeList
        If IDtest = i.getElementsByTagName("SourceID")(0).nodeValue Then
            Dim IDnode
            Dim Sourcenode
            Dim Destnode
            Set SourceID = i.getElementsByTagName("SourceID")(0)
            Set SourceName = i.getElementsByTagName("SourceName")(0)
            Set DestName = i.getElementsByTagName("DestName")(0)

            If SourceID.nodeValue <> "" Then
                IDnode = SourceID.nodeValue
            Else
                IDnode = "ERROR:Undefined ID"
            End If
            If SourceName.nodeValue <> "" Then
                Sourcenode = SourceName.nodeValue
            Else
                Sourcenode = "(No Source Name in config files)"
            End If
            If DestName.nodeValue <> "" Then
                Destnode = DestName.nodeValue
            Else
                Destnode = "(No Dest Name in config files)"
            End If

            AddVendorDetails = IDnode & vbCrLf
            AddVendorDetails = AddVendorDetails & "SourceName: " & Sourcenode & vbCrLf
            AddVendorDetails = AddVendorDetails & "DestName: " & Destnode & vbCrLf
            If SourceID.nodeValue <> "" Then
                MsgBox (AddVendorDetails)
            End If
            strTempSourceIDs = Replace(strTempSourceIDs, IDnode, AddVendorDetails)
        End If
    Next
Next

strTempSourceIDs = "######################## " & WeekdayName(Weekday(Date)) & Date & " ########################" & vbCrLf & strTempSourceIDs
MsgBox (strTempSourceIDs)

Answer 1

As you already found out yourself, the property you need to check is text, not nodeValue. Also, I'd recommend using Msxml2.DOMDocument and SelectNodes with an XPath expression instead of getElement* methods and the deprecated Microsoft.XMLDOM.

Set objXMLDoc = CreateObject("Msxml2.DOMDocument.6.0")
objXMLDoc.Async = False
objXMLDoc.Load "C:\path\to\VendorConfig.xml"
...
For Each node In objXMLDoc.SelectNodes("//SourceID")
  ...
Next

To ensure uniqueness of the IDs load the file temp-4.txt into a dictionary:

listfile = "C:\path\to\temp-4.txt"

Set fso    = CreateObject("Scripting.FileSystemObject")
Set idList = CreateObject("Scripting.Dictionary")
For Each id In Split(fso.OpenTextFile(listfile).ReadAll, vbNewLine)
  idList(Trim(id)) = True
Next

Update the list with the IDs from your XML the same way:

For Each node In objXMLDoc.SelectNodes("//SourceID")
  idList(Trim(node.text)) = True
Next

then write the keys from the dictionary back to the file:

fso.OpenTextFile(listfile, 2).Write Join(idList.Keys, vbNewLine)

Answer 2

The getElementsByTagName isn't working so IDtest is not getting the value of the SourceID.

If i code it like this, it works.

    Set objXMLDoc = CreateObject("Microsoft.XMLDOM") 
objXMLDoc.async = False 
objXMLDoc.load("C:\users\jdarling\20161109\01\VendorConfig.xml")
If objXMLDoc.parseError.errorCode <> 0 Then
    MsgBox ("XML connection error detected.")
End If

Set Root = objXMLDoc.documentElement 
Set NodeList = Root.getElementsByTagName("Vendor") 

Dim AddVendorDetails, InputFile, FSO, oFile, strTempSourceIDs 
InputFile = "C:\users\jdarling\20161109\01\temp-4.txt" 
Set FSO = CreateObject("Scripting.FileSystemObject") 
Set oFile = FSO.OpenTextFile(InputFile) 
strTempSourceIDs = oFile.ReadAll 
oFile.Close 

Dim splitCounter
Dim splitCatcher
Dim IDtest

SplitCatcher = Split(strTempSourceIDs, vbCrLf)
For splitCounter = 0 To UBound(SplitCatcher)
    IDtest = SplitCatcher(splitCounter)

    For Each i In NodeList 

        'Debug.WriteLine "Comparing: " & Trim(IDtest)& "  TO   " & Trim(i.firstChild.nodeTypedValue)

        If Trim(IDtest) =  Trim(i.firstChild.nodeTypedValue) Then

            Debug.WriteLine "FOUND! " & IDTest

            Dim IDnode
            Dim Sourcenode
            Dim Destnode
            Set SourceID = i.getElementsByTagName("SourceID")(0)
            Set SourceName = i.getElementsByTagName("SourceName")(0)
            Set DestName = i.getElementsByTagName("DestName")(0)

            If SourceID.nodeValue <> "" Then
                IDnode = SourceID.nodeValue
            Else
                IDnode = "ERROR:Undefined ID"
            End If
            If SourceName.nodeValue <> "" Then
                Sourcenode = SourceName.nodeValue
            Else
                Sourcenode = "(No Source Name in config files)"
            End If
            If DestName.nodeValue <> "" Then
                Destnode = DestName.nodeValue
            Else
                Destnode = "(No Dest Name in config files)"
            End If

            AddVendorDetails = IDnode & vbCrLf 
            AddVendorDetails = AddVendorDetails & "SourceName: " & Sourcenode & vbCrLf 
            AddVendorDetails = AddVendorDetails & "DestName: " & Destnode & vbCrLf 
            If SourceID.nodeValue <> "" Then
                MsgBox (AddVendorDetails)
            End If    
            strTempSourceIDs = Replace(strTempSourceIDs, IDnode, AddVendorDetails)
        End If
    Next
Next

strTempSourceIDs = "######################## " & WeekdayName(Weekday(Date)) & Date & " ########################" & vbCrLf & strTempSourceIDs
MsgBox (strTempSourceIDs)