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

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

fb1990

ASKER

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

fb1990

ASKER

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

fb1990

ASKER

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

ste5an

Then change the XPath selector..

fb1990

ASKER

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fb1990

ASKER

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

fb1990

ASKER

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

fb1990

ASKER

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

RSS.accdb

fb1990

ASKER

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

fb1990

ASKER

Thank you very much!