Avatar of fb1990
fb1990 asked on

Retrieving RSS Feed via XML url to Access table

Hello EE,

Is there a way to created an access database and export all reviews for an app from the iTunes App Store into in a MS access table?  I have this URL:

https://itunes.apple.com/gb/rss/customerreviews/id=317212648/sortBy=mostRecent/xml

I have a list ID's in a table in the access database, i want to iterate through the ID field (possibly through a form) and pull the following data into another table with the following fields extracted from the data.  I have another table with the ID listed in a field called ID.  Here are the fields that I need from the xml file
Updated ID Title Content As Comments Votesum VoteCount Rating Author Name
Sample Image
Microsoft Access

Avatar of undefined
Last Comment
fb1990

8/22/2022 - Mon
ste5an

E.g.

Option Compare Database
Option Explicit

Public Sub TestRss1()

  Dim rit As clsRssITunes
  
  Set rit = New clsRssITunes
  rit.DebugLoadFromFile "C:\Temp\xml.xml"
  Set rit = Nothing
 
End Sub

Public Sub TestRss2()

  Dim rit As clsRssITunes
  
  Set rit = New clsRssITunes
  rit.DebugLoadFromUrl
  Set rit = Nothing
 
End Sub

Open in new window


with

' Reference required: Microsoft XML, v6.0

Option Compare Database
Option Explicit

Private Const RSS_URL As String = "https://itunes.apple.com/gb/rss/customerreviews/id=317212648/sortBy=mostRecent/xml"

Private m_XDocument As MSXML2.DOMDocument60

Public Sub DebugLoadFromFile(AFileName As String)

  LoadFromFile AFileName
  ExtractHeader XDocumentNode("/a:feed")
  ExtractEntries
  
End Sub

Public Sub DebugLoadFromUrl()

  LoadFromUrl RSS_URL
  ExtractHeader XDocumentNode("/a:feed")
  ExtractEntries
  
End Sub

Private Sub ExtractEntries()

  Dim Entry As MSXML2.IXMLDOMNode
  
  For Each Entry In XDocumentNodes("/a:feed/a:entry")
    ExtractEntry Entry
  Next Entry

End Sub

Private Sub ExtractEntry(AEntry As MSXML2.IXMLDOMNode)

  Dim Rating As String
  Dim Vote As String
  
  ExtractHeader AEntry
  
  Rating = XElement(XNode(AEntry, "im:rating"))
  Vote = XElement(XNode(AEntry, "im:voteSum"))
  
  Debug.Print Rating
  Debug.Print Vote
  
End Sub

Private Sub ExtractHeader(ANode As MSXML2.IXMLDOMNode)

  Dim ID As String
  Dim Title As String
  Dim Updated As String
    
  ID = XElement(XNode(ANode, "a:id"))
  Title = XElement(XNode(ANode, "a:title"))
  Updated = XElement(XNode(ANode, "a:updated"))
  
  Debug.Print ID
  Debug.Print Title
  Debug.Print Updated

End Sub

Public Sub LoadFromFile(AFileName As String)

  InitializeLoading
  m_XDocument.Load AFileName
  FinalizeLoading
  
End Sub

Public Sub LoadFromUrl(AUrl As String)

  InitializeLoading
  m_XDocument.Load AUrl
  FinalizeLoading
  
End Sub

Private Sub FinalizeLoading()

  m_XDocument.SetProperty _
    "SelectionNamespaces", _
    "xmlns:a='http://www.w3.org/2005/Atom' xmlns:im='http://itunes.apple.com/rss'"
  
End Sub

Private Sub InitializeLoading()

  Set m_XDocument = Nothing
  Set m_XDocument = New MSXML2.DOMDocument60
  m_XDocument.async = False
  
End Sub

Private Function XDocumentNode(AXPath As String) As MSXML2.IXMLDOMNode

  Set XDocumentNode = m_XDocument.selectSingleNode(AXPath)

End Function

Private Function XDocumentNodes(AXPath As String) As MSXML2.IXMLDOMNodeList

  Set XDocumentNodes = m_XDocument.selectNodes(AXPath)

End Function

Private Function XNode(ANode As MSXML2.IXMLDOMNode, AXPath As String) As MSXML2.IXMLDOMNode

  Set XNode = ANode.selectSingleNode(AXPath)

End Function

Private Function XNodes(ANode As MSXML2.IXMLDOMNode, AXPath As String) As MSXML2.IXMLDOMNodeList

  Set XNodes = ANode.selectNodes(AXPath)

End Function

Private Function XAttribute(ANode As MSXML2.IXMLDOMNode, AAttributeName As String) As String

  On Local Error Resume Next
    
  XAttribute = ANode.Attributes.getNamedItem(AAttributeName).Text

End Function

Private Function XElement(ANode As MSXML2.IXMLDOMNode) As String

  On Local Error Resume Next
  
  XElement = ANode.Text

End Function

Open in new window

ASKER
fb1990

Hello Ste5an,

Can you help me further with this solution?  Is there a way you can demonstrate with the example IDs given here.

389801252
317212648

Thanks,
ste5an

E.g.

' Reference required: Microsoft XML, v6.0

Option Compare Database
Option Explicit

Private Const RSS_URL As String = "https://itunes.apple.com/gb/rss/customerreviews/id=317212648/sortBy=mostRecent/xml"

Private m_XDocument As MSXML2.DOMDocument60

Public Sub DebugLoadFromFile(AFileName As String)

  LoadFromFile AFileName
  
  ExtractEntriesByID 389801252
  ExtractEntriesByID 317212648
  
End Sub

Public Sub DebugLoadFromUrl()

  LoadFromUrl RSS_URL
  ExtractHeader XDocumentNode("/a:feed")
  ExtractEntries
  
End Sub

Private Sub ExtractEntriesByID(AID As Long)

  Dim Entry As MSXML2.IXMLDOMNode
  
  For Each Entry In XDocumentNodes("//a:entry[a:id/@im:id='" & CStr(AID) & "']")
    ExtractEntry Entry
  Next Entry

End Sub

Private Sub ExtractEntries()

  Dim Entry As MSXML2.IXMLDOMNode
  
  For Each Entry In XDocumentNodes("/a:feed/a:entry")
    ExtractEntry Entry
  Next Entry

End Sub

Private Sub ExtractEntry(AEntry As MSXML2.IXMLDOMNode)

  Dim ID As String
  Dim Rating As String
  Dim Vote As String
  
  ExtractHeader AEntry
  
  ID = XAttribute(XNode(AEntry, "a:id"), "im:id")
  Rating = XElement(XNode(AEntry, "im:rating"))
  Vote = XElement(XNode(AEntry, "im:voteSum"))
  
  Debug.Print ID
  Debug.Print Rating
  Debug.Print Vote
  
End Sub

Private Sub ExtractHeader(ANode As MSXML2.IXMLDOMNode)

  Dim ID As String
  Dim Title As String
  Dim Updated As String
    
  ID = XElement(XNode(ANode, "a:id"))
  Title = XElement(XNode(ANode, "a:title"))
  Updated = XElement(XNode(ANode, "a:updated"))
  
  Debug.Print ID
  Debug.Print Title
  Debug.Print Updated

End Sub

Public Sub LoadFromFile(AFileName As String)

  InitializeLoading
  m_XDocument.Load AFileName
  FinalizeLoading
  
End Sub

Public Sub LoadFromUrl(AUrl As String)

  InitializeLoading
  m_XDocument.Load AUrl
  FinalizeLoading
  
End Sub

Private Sub FinalizeLoading()

  m_XDocument.SetProperty _
    "SelectionNamespaces", _
    "xmlns:a='http://www.w3.org/2005/Atom' xmlns:im='http://itunes.apple.com/rss'"
  
End Sub

Private Sub InitializeLoading()

  Set m_XDocument = Nothing
  Set m_XDocument = New MSXML2.DOMDocument60
  m_XDocument.async = False
  
End Sub

Private Function XDocumentNode(AXPath As String) As MSXML2.IXMLDOMNode

  Set XDocumentNode = m_XDocument.selectSingleNode(AXPath)

End Function

Private Function XDocumentNodes(AXPath As String) As MSXML2.IXMLDOMNodeList

  Set XDocumentNodes = m_XDocument.selectNodes(AXPath)

End Function

Private Function XNode(ANode As MSXML2.IXMLDOMNode, AXPath As String) As MSXML2.IXMLDOMNode

  Set XNode = ANode.selectSingleNode(AXPath)

End Function

Private Function XNodes(ANode As MSXML2.IXMLDOMNode, AXPath As String) As MSXML2.IXMLDOMNodeList

  Set XNodes = ANode.selectNodes(AXPath)

End Function

Private Function XAttribute(ANode As MSXML2.IXMLDOMNode, AAttributeName As String) As String

  On Local Error Resume Next
    
  XAttribute = ANode.Attributes.getNamedItem(AAttributeName).Text

End Function

Private Function XElement(ANode As MSXML2.IXMLDOMNode) As String

  On Local Error Resume Next
  
  XElement = ANode.Text

End Function

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
fb1990

Hello

I have a list ID's in a table in the access database, i want to iterate through the ID field (possibly through a form and button ) I have placed example of the access database here with 2 tables.  The MS access can contain a form and 2 tables tblID and tblCustReviews.

Is it possible to iterate through the tbIID to grab the ID and then add to the URL and pull data into the tblCustReviews table?
RSS.accdb
ste5an

I see. The code in the second file is a class file..
RSS.accdb
ASKER
fb1990

Hello Ste5an,

The application is not pulling the correct data.  The records that i ineed to pull are here
</entry>
<entry>
<updated>2016-03-05T04:07:00-07:00</updated>
<id>1343094249</id>
<title>Brill</title>
<content type="text">
Love how easy it is to use. Love that you can look for just films aswell. It's really handy As I can connect it to my virgin box and set a program on record through my phone when out of the house. Love it!!
</content>
<im:contentType term="Application" label="Application"/>
<im:voteSum>0</im:voteSum>
<im:voteCount>0</im:voteCount>
<im:rating>4</im:rating>
<im:version>5.9.4</im:version>
<author>
<name>BeckaLou90</name>
<uri>https://itunes.apple.com/gb/reviews/id296501531</uri>
</author>

Open in new window



and more for each ID in the tblID
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ste5an

Then change the XPath selector..
ASKER
fb1990

hello Ste5an,

Can you please tell me which line of the code to change and what to change it to?

Thanks
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
fb1990

wow! you have surprised my imagination.  I will award the point.  One question i had is:

Is there away to loop through the IDs?  It is only bringing in the records for one ID: 317212648 at the moment.  I will more IDs and wants to loop/iterate through all the ids

Thank you thus far
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
fb1990

Thank you very much for staying with me on this.  I really appreciate your help.  I need to add the version field, but I think I can tinker with what you have provided me here
ASKER
fb1990

Hello Ste5an,

I just realized that i needed to add the Identitier which will be ID of of the record being pulled like 317212648 as shown in the URL.  Is this possible?  I can open another question for that so that you can help add
ste5an

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
fb1990

Hello Ste5an,

I think you misunderstand my question.  The ID of the specific review is different from the ID on the URL.    The ID of the URL is the ID that is in my tblID not the ID of the specific review.  In this example there are 2 IDs

https://itunes.apple.com/us/rss/customerreviews/id=317212648/sortBy=mostRecent/xml
https://itunes.apple.com/us/rss/customerreviews/id=389801252/sortBy=mostRecent/xml

Open in new window


What i planned to do is place the ID in the URL inside the tblID and loop through that and pulled the xml record into the tblCustReviews table.

Does this make sense?  I think I am not been clear.

Also, I deleted the records from the table to test drive the database, when I clicked the button to reimport nothing happens.  If you are able to fix this for me, can you add version to the fields for me?

My apologies
ste5an

I think you misunderstand my question.
Nope.

I think I am not been clear.
Yup.

Using the same name (ID) in different contexts is an problem. Cause from the relational model the same name means the same entity or attribute. So this is something everyone needs to learn, when its about databases and the relational model: Be precise.
RSS.accdb
ASKER
fb1990

Thank you very much!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23