SQL 2008 XML output task

Posted on 2011-10-29
Last Modified: 2012-05-12
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?
Question by:sgray128
    LVL 12

    Accepted Solution

    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
    LVL 12

    Expert Comment

    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.
    LVL 2

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now