Solved

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

Posted on 2006-10-23
6
309 Views
Last Modified: 2008-02-01
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
Comment
Question by:Andrew Angell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17792255
Probably your best bet is to add the values to recordset you create on the fly and then select unique values only.
0
 
LVL 11

Author Comment

by:Andrew Angell
ID: 17792557
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17793313
>> 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!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17793323
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
 
LVL 11

Author Comment

by:Andrew Angell
ID: 17793413
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17795636
>>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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 100
Record locking on classic ASP 3 40
Read text on Table 7 29
JS to redirect to prev page 8 23
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question