Parsing XML with vbscript

Hello Experts,
I have a program that outputs an XML file based upon the information a user enters into online form.  I am trying to parse this XML file using vbscript.  Within the XML file there is a field called EMAILDISTRIBUTIONLIST that will have an e-mail address entered into it (this e-mail address will be changing every time I parse the file).  What I need is a way to parse the file and get the email address associated with that EMAILDISTRIBUTIONLIST field.  I have attached a sample XML file for your reference.  In looking at multiple XML files, I have noticed that the role id of "e5f72d7e-2ca7-418b-bfd0-7fa5a63a4a14" is always the same from xml to xml - so that might help in parsing out the info if the field name can't be used for some reason.  

I had gotten this far, but it doesn't work:

'On Error Resume Next
Dim xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
'Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")
xmldoc.async = False
xmldoc.load("c:\temp\xml\xmltest1.xml")
xmldoc.setProperty "SelectionLanguage", "XPath"

If 0 = xmldoc.parseError Then
    Dim sXPath
      sXPath = "/request/participants/role[@id='e5f72d7e-2ca7-418b-bfd0-7fa5a63a4a14']"
    Dim ndlWantedHosts
      Set ndlWantedHosts = xmldoc.selectNodes(sXPath)
    If 0 = ndlWantedHosts.length Then
        WScript.Echo sXPath, "failed due to length"
    Else
        Dim ndWantedHost
        For Each ndWantedHost In ndlWantedHosts
            WScript.Echo "email address:", ndWantedHosts.parentNode.selectSingleNode("email").text
        Next
    End If
Else
    WScript.Echo xmldoc.parseError.reason
End If
SampleXML.xml
Paul TiffanyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shiju SasidharanAssoc Project ManagerCommented:
Can you upload a valid xml file? that file seems have missing tags.
0
Paul TiffanyAuthor Commented:
Unfortunately I can't put the whole XML in as it contains sensitive company information - but what is shown is pretty accurate of what is in the XML created by the program - just has the few sections minimized and has domain name information removed.
0
Shiju SasidharanAssoc Project ManagerCommented:
Please try with this path

sXPath = "//result/data/request/participants/participant[@id='896e0003-1280-48a2-9c52-4e8c6561c9d5']"
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Paul TiffanyAuthor Commented:
When trying that, I get "failed due to length"
0
Paul TiffanyAuthor Commented:
Also, I can't filter on the participant ID - that will change from xml to xml - the role ID stays the same.
0
Shiju SasidharanAssoc Project ManagerCommented:
<email>NEEDTHISEMAIL@us.test.com</email>
Above set of tag is inside Participant tag not within role tag.

if you are looking to extract <name>EMAILDISTRIBUTIONLIST</name> then try this path

sXPath = "//result/data/request/participants/participant/role[@id='e5f72d7e-2ca7-418b-bfd0-7fa5a63a4a14']"
0
Paul TiffanyAuthor Commented:
I need to extract the email address within the email tag - in this example case, NEEDTHISEMAIL@us.test.com
0
Paul TiffanyAuthor Commented:
What if the "@us.test.com" domain on the e-mail address will always be the same - would it be possible to search for that and grab the rest of the e-mail address?
0
Shiju SasidharanAssoc Project ManagerCommented:
'On Error Resume Next
Dim xmldoc
Set xmldoc = CreateObject("Microsoft.XMLDOM")
'Set xmldoc = CreateObject("MSXML2.DOMDocument.6.0")
xmldoc.async = False
xmldoc.Load "c:\temp\xml\xmltest1.xml"
xmldoc.setProperty "SelectionLanguage", "XPath"

If 0 = xmldoc.parseError Then
    Dim sXPath
      sXPath = "//result/data"
    Dim ndlWantedHosts
      Set ndlWantedHosts = xmldoc.SelectNodes(sXPath)
    If ndlWantedHosts.Length = 0 Then
        WScript.Echo sXPath, "failed due to length"
    Else
        'MsgBox ndlWantedHosts.Item(0).XML
        Dim strXml
        strXml = ndlWantedHosts.Item(0).XML
        Dim objRegEx
        Set objRegEx = CreateObject("VBScript.RegExp")
        objRegEx.IgnoreCase = True
        objRegEx.Pattern = "[A-Z0-9._%+-]+@us\.test\.com"
        objRegEx.Global = True
        If objRegEx.Test(strXml) Then
           MsgBox objRegEx.Execute(strXml).Item(0)
        Else
          MsgBox "Email address: Not found!"
        End If
    End If
Else
    WScript.Echo xmldoc.parseError.reason
End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul TiffanyAuthor Commented:
Thanks for the help - this worked perfectly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

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.