Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Parse only unique values from an XML response using XMLDOM...???

I'm parsing an XML response like so:

'open the XML document
      Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
      xmlDoc.Async = false
      xmlDoc.LoadXML(APIResponse)
      
'parse data from response
      Set RecordsNode = xmlDoc.selectNodes("/fmresultset/resultset/record")
            For Each recordNode In RecordsNode
                  EmailAddress = TryNode(recordNode, "field[@name='Leads_Clients::Email_Address']/data")
                  Response.Write(EmailAddress & "<br>")
            Next
      Set RecordsNode = Nothing

It works great, however, many of the email addresses returned in this are for the same person.  (the request grabs all emails for customers who have purchased a particular item from us, and many have purchased multiple similar items.)

I need to parse out only unique values, so basically I need the equivalent of a SQL SELECT DISTINCT.

Is there a way to do that from within XMLDOM?  Any information would be greatly appreciated.  thanks!
0
Andrew Angell
Asked:
Andrew Angell
  • 4
  • 2
1 Solution
 
Anthony PerkinsCommented:
Probably your best bet is to add the values to recordset you create on the fly and then select unique values only.
0
 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
Can you guide me on getting the values into a recordset?  I'm a little confused there since I'm not actually selecting from a DB at that point.
0
 
Anthony PerkinsCommented:
>> I'm a little confused there since I'm not actually selecting from a DB at that point.<<
You don't have to be.

I suspect there is a better way of skinning this cat, but the following example certainly works:

Const adVarchar = 200

Dim rs
Dim EmailAddress, EmailAddresses

EmailAddresses = Array("myemail@domain.com", "username@domain.com", "anotheruser@domain.com", "myemail@domain.com", "anotheruser@domain.com")         ' Create some email addresses with duplicates

Set rs = CreateObject("ADODB.Recordset")
rs.Fields.Append "Email_Address", adVarchar, 1000
rs.Open

For Each EmailAddress In EmailAddresses
    rs.AddNew
    rs.Fields("Email_Address").Value = EmailAddress
    rs.Update
Next

rs.Sort = "Email_Address"
EmailAddress = ""
Do While Not rs.EOF
    If StrComp(rs.Fields("Email_Address").Value, EmailAddress, vbTextCompare) <> 0 Then
        EmailAddress = rs.Fields("Email_Address").Value
        Debug.Print EmailAddress
    End If
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
So in your case it would look something like this:

Const adVarchar = 200

Dim rs
Dim EmailAddress

Set rs = CreateObject("ADODB.Recordset")
rs.Fields.Append "Email_Address", adVarchar, 1000
rs.Open

'open the XML document
Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.Async = false
xmlDoc.LoadXML(APIResponse)
     
'parse data from response
Set RecordsNode = xmlDoc.selectNodes("/fmresultset/resultset/record")
For Each recordNode In RecordsNode
      rs.AddNew
      EmailAddress = TryNode(recordNode, "field[@name='Leads_Clients::Email_Address']/data")
      rs.Fields("Email_Address").Value = EmailAddress
      rs.Update
Next
Set RecordsNode = Nothing

rs.Sort = "Email_Address"
EmailAddress = ""
Do While Not rs.EOF
      If StrComp(rs.Fields("Email_Address").Value, EmailAddress) <> 0 Then
            EmailAddress = rs.Fields("Email_Address").Value
            Response.Write(EmailAddress & "<br>")
      End If
      rs.MoveNext
Loop
rs.Close
Set rs = Nothing
0
 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
Works great and I learned something too.  Thanks!  Would you mind explanining a little bit about the Const adVarchar parts?  I've never used that and don't know what it does.  thanks!
0
 
Anthony PerkinsCommented:
>>Would you mind explanining a little bit about the Const adVarchar parts?  I've never used that and don't know what it does.<<
Not at all.  In order to build a recordset on the fly you need to define the data types for each field.  adVarchar is just an ADO constant that defines a (you got it) varchar (variable length string).  See here for a complete list of ADO constants:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now