Using TSQL to Read an RSS Feed

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:

SteveStedman5
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!

4 Comments on “Using TSQL to Read an RSS Feed

  1. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

*