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
BEGIN
       SET NOCOUNT ON;
       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]
      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/&gt;.            —> <!— 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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 19 other followers

Archives

Top Rated

Blog Stats

  • 169,280 hits

%d bloggers like this: