ColdFusion tutorial to export from database to RSS

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
BEGIN
       SET NOCOUNT ON;
       SELECT            1 AS tag 
                       , NULL AS parent
                        , ‘2.0′ AS
                        , 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]
      UNION
      SELECT            2 AS tag
                        , 1 AS parent
                        , NULL
                        , NULL
                        , ‘The name of your bulk upload.’ – title
                        , ‘A description of your content.’ – description
                        , ‘http://www.clickfind.com.au’ – link
                        , ‘en-au’ – language
                        , ‘rss-feed@clickfind.com.au’ – web master
                        , CONVERT( VARCHAR( 50 ), GETDATE() ) + ‘ GMT‘ – publication date
                        , ‘clickfind.com.au’ – generator
                        , 60 – in minutes
                        , NULL
                        , NULL
                        , NULL
                        , NULL
      UNION
      SELECT            3 AS tag
                        , 2 AS parent
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , NULL
                        , [title]
                        , [description]
                        , ‘http://www.clickfind.com.au/product/listing.cfm?productIdentity=’ + CAST( productIdentity AS VARCHAR( 10 ) )
                        , productIdentity 
           FROM        [your table name here] 
           FOR XML EXPLICIT END
  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>http://www.clickfind.com.au</link>    <language>en-au</language>    <webMaster>rss-feed@clickfind.com.au</webMaster>    <pubDate>Jan  5 2008  2:35PM GMT</pubDate>    <generator>clickfind.com.au</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>http://www.clickfind.com.au/product/listing.cfm?productIdentity=63245</link>      <guid>63245</guid>    </item>    <item>      <title>1 x decorated weight</title>      <description>Balloons 1 x decorated weight</description>      <link>http://www.clickfind.com.au/product/listing.cfm?productIdentity=62801</link>      <guid>62801</guid>    </item>    <item>      <title>1 x standard weight</title>      <description>Balloons 1 x standard weight</description>      <link>http://www.clickfind.com.au/product/listing.cfm?productIdentity=62802</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 (clickfind.com.au) *****            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 <http://www.gnu.org/licenses/>.            —> <!— call the stored procedure —>

<cfstoredproc
             procedure=”sp_rssProductFeed“
            datasource=”your datasource name here“>
            <!— assign a name to the resultset —>
            <cfprocresult
                        name=”rsRSSFeed“
                        resultset=”1“></cfstoredproc>
<cfscript>           
// 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 ] ];
</cfscript>
<!— write the file to disk —>
<cffile
            action=”write”
            addnewline=”yes”
            file=”#storagePath#product-rss.xml”
            output=”#rssFeed#”
            fixnewline=”no”> 

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 coldfusion-tutorial@clickfind.com.au 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