(SQL Server) Read RSS Feed

Sample code showing how to read an RSS feed and emit the contents.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @rss int
    -- Use "Chilkat_9_5_0.Rss" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rss', @rss OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Download from the feed URL:
    DECLARE @success int
    EXEC sp_OAMethod @rss, 'DownloadRss', @success OUT, 'http://www.nytimes.com/services/xml/rss/nyt/HomePage.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rss, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rss
        RETURN
      END

    -- Get the 1st channel.
    DECLARE @rssChannel int
    EXEC sp_OAMethod @rss, 'GetChannel', @rssChannel OUT, 0
    EXEC sp_OAGetProperty @rss, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No channel found in RSS feed.'
        EXEC @hr = sp_OADestroy @rss
        RETURN
      END

    -- Display the various pieces of information about the channel:

    EXEC sp_OAMethod @rssChannel, 'GetString', @sTmp0 OUT, 'title'
    PRINT 'Title: ' + @sTmp0

    EXEC sp_OAMethod @rssChannel, 'GetString', @sTmp0 OUT, 'link'
    PRINT 'Link: ' + @sTmp0

    EXEC sp_OAMethod @rssChannel, 'GetString', @sTmp0 OUT, 'description'
    PRINT 'Description: ' + @sTmp0

    -- For each item in the channel, display the title, link,
    -- publish date, and categories assigned to the post.
    DECLARE @numItems int
    EXEC sp_OAGetProperty @rssChannel, 'NumItems', @numItems OUT
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @numItems - 1
      BEGIN
        DECLARE @rssItem int
        EXEC sp_OAMethod @rssChannel, 'GetItem', @rssItem OUT, @i


        PRINT '----'

        EXEC sp_OAMethod @rssItem, 'GetString', @sTmp0 OUT, 'title'
        PRINT 'Title: ' + @sTmp0

        EXEC sp_OAMethod @rssItem, 'GetString', @sTmp0 OUT, 'link'
        PRINT 'Link: ' + @sTmp0

        EXEC sp_OAMethod @rssItem, 'GetString', @sTmp0 OUT, 'pubDate'
        PRINT 'pubDate: ' + @sTmp0

        DECLARE @numCategories int
        EXEC sp_OAMethod @rssItem, 'GetCount', @numCategories OUT, 'category'
        DECLARE @j int

        IF @numCategories > 0
          BEGIN
            SELECT @j = 0
            WHILE @j <= @numCategories - 1
              BEGIN

                EXEC sp_OAMethod @rssItem, 'MGetString', @sTmp0 OUT, 'category', @j
                PRINT '    category: ' + @sTmp0
                SELECT @j = @j + 1
              END

          END
        EXEC @hr = sp_OADestroy @rssItem

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @rssChannel


    EXEC @hr = sp_OADestroy @rss


END
GO