[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

SQL 2008 XML output task

Sirs:
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">
<channel>

<title>The Channel Title Goes Here</title>
<description>The explanation of how the items are related goes here</description>
<link>http://www.directoryoflinksgohere</link>

<item>
<title>The Title Goes Here</title>
<description>The description goes here</description>
<link>http://www.linkgoeshere.com</link>
</item>

<item>
<title>Another Title Goes Here</title>
<description>Another description goes here</description>
<link>http://www.anotherlinkgoeshere.com</link>
</item>

</channel>
</rss>

But I can't figure out how to form a SQL statement to do that.
Is it possible?
0
sgray128
Asked:
sgray128
  • 2
1 Solution
 
sachitjainCommented:
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
begin
      select @title = title, @description = [description], @link = link
      from @table where [SNO] = @i
      set @retXML = @retXML + '<item>'
      if @title is null
            set @retXML = @retXML + '<title/>'
      else
            set @retXML = @retXML + '<title>' + @title + '</title>'
      if @description is null
            set @retXML = @retXML + '<description/>'
      else
            set @retXML = @retXML + '<description>' + @description + '</description>'
      if @link is null
            set @retXML = @retXML + '<link/>'
      else
            set @retXML = @retXML + '<link>' + @link + '</link>'
      set @retXML = @retXML + '</item>'
      set @i = @i + 1
end
set @retXML = @retXML + '</channel></rss>'
print @retXML
0
 
sachitjainCommented:
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.
0
 
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...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now