SQL 2008 XML output task

I'm trying to generate an RSS feed using SQL 2008
I have a table with fields for title, description, and link. All the other fields can be hard coded, they don't change.

I need the output to look like this:
<?xml version="1.0"?>
<rss version="2.0">

<title>The Channel Title Goes Here</title>
<description>The explanation of how the items are related goes here</description>

<title>The Title Goes Here</title>
<description>The description goes here</description>

<title>Another Title Goes Here</title>
<description>Another description goes here</description>


But I can't figure out how to form a SQL statement to do that.
Is it possible?
Who is Participating?
sachitjainConnect With a Mentor Commented:
You could write some logic like following to achieve the desired output

set nocount on
declare @table table ([SNo] int identity(1,1), [title] varchar(100), [description] varchar(500), [link] varchar(400))
insert into @table (title, [description], link)
select title, [description], link from <YOUR_TABLE_NAME>

declare @count int, @i int, @retXML varchar(max)
declare @title varchar(100), @description varchar(500), @link varchar(400)

select @count = count(*) from @table
set @retXML = '<?xml version="1.0"?><rss version="2.0"><channel>'

set @i = 1

while @i <= @count
      select @title = title, @description = [description], @link = link
      from @table where [SNO] = @i
      set @retXML = @retXML + '<item>'
      if @title is null
            set @retXML = @retXML + '<title/>'
            set @retXML = @retXML + '<title>' + @title + '</title>'
      if @description is null
            set @retXML = @retXML + '<description/>'
            set @retXML = @retXML + '<description>' + @description + '</description>'
      if @link is null
            set @retXML = @retXML + '<link/>'
            set @retXML = @retXML + '<link>' + @link + '</link>'
      set @retXML = @retXML + '</item>'
      set @i = @i + 1
set @retXML = @retXML + '</channel></rss>'
print @retXML
You need to correct datatypes and lengths of fields title, description and link as well as YOUR_TABLE_NAME in this logic as per your needs before adopting it. Moreover if count of records is greater than equal to thousands in your table then use temp table instead of table variable @table for better performance. I hope it helps.
sgray128Author Commented:
Points for responding. Actually, I had already coded something like that. I was hoping for an XML Path answer - all in one statement. I didn't think it could be done, but wanted to ask.

Maybe in the next version...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.