ColdFusion tutorial to export from database to RSS

January 5, 2008 at 5:09 am Leave a comment

Always wanted to export items from your existing database into RSS with ColdFusion and syndicate it, but don’t know how? 

Well, this is your lucky day! We’re going to show you how you can easily export your database records to RSS. 

We’ll make some assumptions in this article to make things easier, and they are;

          you are running MS SQL that support “FOR XML”
          you are running ColdFusion  

Ready to get started? 

The first thing to do is go into your MS SQL database and create the following stored procedure that creates the RSS directly in your RDBMS. 

CREATE PROCEDURE [dbo].[sp_rssProductFeed]AS
       SELECT            1 AS tag 
, NULL AS parent
                        , ‘2.0’ AS [rss!1!version]
                        , NULL AS [channel!2]
                        , NULL AS [channel!2!title!element]
                        , NULL AS [channel!2!description!element]
                        , NULL AS [channel!2!link!element]
                        , NULL AS [channel!2!language!element]
                        , NULL AS [channel!2!webMaster!element]
                        , NULL AS [channel!2!pubDate!element]
                        , NULL AS [channel!2!generator!element]
                        , NULL AS [channel!2!ttl!element]
                        , NULL AS [item!3!title!element]
                        , NULL AS [item!3!description!element]
                        , NULL AS [item!3!link!element]
                        , NULL AS [item!3!guid!element]
      SELECT            2 AS tag
                        , 1 AS parent
                        , NULL
                        , NULL
                        , ‘The name of your bulk upload.’ — title
                        , ‘A description of your content.’ — description
                        ,’ — link
                        , ‘en-au’ — language
                        , ‘’ — web master
                        , CONVERT( VARCHAR( 50 ), GETDATE() ) + GMT — publication date
                        , ‘’ — generator
                        , 60 — in minutes
                        , NULL
                        , NULL
                        , NULL
                        , NULL
      SELECT            3 AS tag
                        , 2 AS parent
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , [title]
                        , [description]
                        ,’ + CAST( productIdentity AS VARCHAR( 10 ) )
                        , productIdentity 
FROM        [your table name here]
  When you execute this stored procedure you should get output like following (excluding the content). 

<rss version=2.0>  <channel>    <title>The name of your bulk upload.</title>    <description>A description of your content.</description>    <link></link>    <language>en-au</language>    <webMaster></webMaster>    <pubDate>Jan  5 2008  2:35PM GMT</pubDate>    <generator></generator>    <ttl>60</ttl>    <item>      <title>0.9m Lightweight Beach Ball</title>      <description>“0.9m Lightweight Beach Ball    There is massive beach and poolside fun to be had with this vinyl multicolored beach ball.      Note: Image shows 1.2m ball. Actual colours may vary, primary colours used are yellow, blue and red.    0.9m in diameter it can be manually inflated or by using a standard foot pump – see  Accessories”</description>      <link></link>      <guid>63245</guid>    </item>    <item>      <title>1 x decorated weight</title>      <description>Balloons 1 x decorated weight</description>      <link></link>      <guid>62801</guid>    </item>    <item>      <title>1 x standard weight</title>      <description>Balloons 1 x standard weight</description>      <link></link>      <guid>62802</guid>    </item>  </channel></rss> 

All pretty simple so far!
The ColdFusion code to call this stored procedure is as following, just copy and paste it into a .cfm file, and run it. 
<!—             ***** Copyright 2007 Commerce Engine Pty Ltd ( *****            You can distribute this code and use it as long as these comments             remain in tact.             This program is free software: you can redistribute it and/or modify            it under the terms of the GNU General Public License as published by            the Free Software Foundation, either version 3 of the License, or            (at your option) any later version.             This program is distributed in the hope that it will be useful,            but WITHOUT ANY WARRANTY; without even the implied warranty of            MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            GNU General Public License for more details.             You should have received a copy of the GNU General Public License            along with this program.  If not, see <;.            —> <!— call the stored procedure —>

            datasource=”your datasource name here“>
            <!— assign a name to the resultset —>
// get the column names data from the resultset
columnName = rsRSSFeed.getMetaData().getColumnLabels();
// create the path where to store the file
storagePath = “c:\replace with where ever you want to store the file\“;
// get the coloumn name
rssFeed = rsRSSFeed[ columnName[ 1 ] ];
<!— write the file to disk —>

And voila! Just a few lines of code and you’re publishing RSS directly from your database. Building this structure through ColdFusion would really slow it down a lot. This actually works with thousands of records. 

I hope you enjoyed this tutorial, any questions, feel free to contact make sure you refer to the original document as we might not know which one you are referring to. 

Feel free to copy and distribute this tutorial, but reference to the author and sponsor. 

Author: Taco Fleur
This article was sponsored by clickfind

Download the Word Document with this tutorial > ColdFusion tutorial to export from database to RSS


Entry filed under: ColdFusion, MS SQL. Tags: , , , , .

New functionality released on clikfind: PageRank™, Alexa Ranking and more… ColdFusion tutorial to create same hash as CF, but in MS SQL!, directly in the MS SQL RDMBS?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 20 other followers


Top Rated

Top Clicks

  • None

Blog Stats

  • 170,012 hits

%d bloggers like this: