Using TSQL to Read an RSS Feed
Most blogs have a RSS feed of some kind. The RSS feed is usually comprised of XML with a list of the most recent posts.
I though it would be handy to try using TSQL to see who has been blogging about what lately. I created two procedures, the first one called httpGET, and another one called LatestBlogPosts. The httpGET procedure as the name might suggest is a simple procedure that just does a HTTP GET to pull data from the web.
The second procedure LatestBlogPost takes the RSS output from the httpGET and parses the XML to do put into a table format so that we can do something useful with it next.
Note: although it is called httpGET, it also works with HTTPS.
Here is some sample code to pull from a RSS feed and extract the blog posts.
CREATE PROCEDURE [dbo].[httpGET]
(
@url NVARCHAR(4000),
@ResponseText NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
BEGIN TRY
DECLARE @Object as Int;
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'GET', @url,'false';
EXEC sp_OAMethod @Object, 'send', null;
DECLARE @TABLEVAR TABLE (responseXml VARCHAR(MAX))
INSERT INTO @TABLEVAR
EXEC sp_OAGetProperty @Object, 'responseText';
SET @ResponseText = '';
SELECT @ResponseText = responseXml FROM @TABLEVAR
END TRY
BEGIN CATCH
print 'Exception in httpGET';
END CATCH
Exec sp_OADestroy @Object;
END
GO
CREATE PROCEDURE [dbo].[LatestBlogPosts]
(
@url NVARCHAR(4000)
)
AS
BEGIN
BEGIN TRY
DECLARE @ResponseText as NVARCHAR(MAX) = '';
EXEC [dbo].[httpGET] @url = @url, @ResponseText = @ResponseText OUTPUT;
DECLARE @xml XML = cast(REPLACE(@ResponseText, 'encoding="UTF-8"', '') as xml);
SELECT x.xmlNode.value('(title)[1]', 'varchar(400)') as BlogTitle,
x.xmlNode.value('(link)[1]', 'varchar(400)') as BlogUrl,
x.xmlNode.value('(pubDate)[1]', 'varchar(400)') as BlogDate,
x.xmlNode.value('(description)[1]', 'varchar(400)') as BlogDescription
FROM @xml.nodes('/rss/channel/item') x(xmlNode)
END TRY
BEGIN CATCH
print 'Exception';
END CATCH
END
GO
EXEC [dbo].[LatestBlogPosts] @url = 'https://stevestedman.com/feed/';
Here is the output when I run it for my site:
Just some TSQL to share with the community.
Enjoy
-Steve Stedman
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Cool stuff Steve! Thanks for sharing.
Thanks for the feedback. Happy to share.
-Steve Stedman
hey, I was taking a look at using this code to pull RSS from Reddit, and I’m having a bit of difficulty getting it to work. Do you have any desire to take a look at this with me?
Hi Aaron, we would be more than happy to help you with this. We actually have service for this type of question. Here is a link to this service called our mentoring package https://stedmansolutions.com/home/store/sql-server-mentoring-4-hour-block/