Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to query and consume WordPress RSS feeds using CLR or xp_cmdshell (cURL)

Views: 593 views
Reading Time: 6 minutes

Hello people,
All right with you ?

I was watching the last video of Fabricio Limawhere he wrote about the blogs he usually follows by feedly, an RSS reader. I found the idea cool and decided to create a small RSS reader running on SQL Server using CLR or cURL running with xp_cmdshell.

In this case, since the return of requests is longer than 4.000 characters in most cases, it is not possible to do so using OLE Automation, which has this limitation.

Because the RSS feed return is basically XML, you will need to have a basic understanding of handling and manipulating strings and XML files. To do so, visit my post SQL Server - How to read, import, and export data from XML files.

One thing worth mentioning is that the amount of records returned by each site is configurable, ie one site for returning the latest 50 posts and another site returning only the latest 10 posts (WordPress default). However, even if the RSS feed is limited to 10 records, you can navigate between feed pages (see below).

How to query WordPress RSS feeds using CLR

To perform this query using CLR (C #), simply use the Stored Procedure stpWs_Requiringwhere source code is available in the post Performing POST and GET requests using CLR (C #) in SQL Server.

If you don't know or want to know more about SQLCLR (C #) in SQL Server, check out these 2 links:

How to enable the use of CLR in your instance:

View source:

How to query WordPress RSS feeds using xp_cmdshell

To perform this query using xp_cmdshell, we will need the cURL binary available on this link.

To be able to use the curl.exe binary without specifying the path, you must either copy it to the C: \ Windows \ System32 directory or enter the full path of curl.exe on your machine.

How to enable the use of xp_cmdshell in your instance:

View source:

Usage example (It is the same for the two SP's - the CLR and xp_cmdshell):

Querying SQL Server Updates

Want to stay on top of SQL Server updates and know when a Service Pack, Cumulative Update, or new version was released?

Meet the blog and follow your feed.

How to create an RSS feed reader in SQL Server

Now I will demonstrate how to create an RSS feed reader, so that it will look at all the posts from the sites you want to follow and send you weekly updates with the new posts. You can customize this reader at will, you can change the periodicity of alerts, creating a category option and sending email, separating posts by categories, etc. Be creative.

In order to create the RSS feed reader below, you will need 2 prerequisites:

Table creation and configuration

RSS reader source code

Example of Email Sent

That's it folks!
I hope you enjoyed.

Regards and see you next post.