ColdFusion Tutorials - EasyCFM.COM

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 [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/>.

--->

<cfstoredproc
            procedure="sp_rssProductFeed"
            datasource="your datasource name here">
            
            <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>

<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 article, 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

All ColdFusion Tutorials By Author: Taco Fleur

ColdFusion tutorial to create same hash as CF, but in MS SQL!
How cool would it be, being able to create the same MD5 hash as ColdFusion, directly in the MS SQL RDMBS?
Author: Taco Fleur
Views: 1,929
Posted Date: Sunday, January 6, 2008
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.
Author: Taco Fleur
Views: 2,061
Posted Date: Saturday, January 5, 2008