We help IT Professionals succeed at work.

Retrieving RSS Feed via XML url to Access table

fb1990
fb1990 asked
on
225 Views
Last Modified: 2016-03-06
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
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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,
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
I see. The code in the second file is a class file..
RSS.accdb

Author

Commented:
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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Then change the XPath selector..

Author

Commented:
hello Ste5an,

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

Thanks
Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:

Author

Commented:
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
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
Thank you very much!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.