Create XML document Using T-SQL Statement

Hi.

There are many ways in asp.net for generating RSS file.you can use string concatenation,loop iteration or using more advanced techniques you can Use LINQ-TO-XML for creating xml document for RSS feed.
i was searching a in SQL server by which i can generate RSS using T-SQL query. SQL Server is shiped with many new function and operators which help us to generate XML from SQL server.

There are many options Like RAW Mode ,AUTO Mode and EXPLICIT Mode.

auto and raw mode can be used for simple xml tag generation ,while Explicit mode is vary powerful mode to generate whole XML documents.

I Will show you the code how to Generate XML file for Rss Feed.

Let's say I have a table Name "myFeeds" which looks like:




Now i want to Create Rss Feed File Using data of this table ,which should look like:




and Here is The code which will give you content as xml which can be written Feed file.

Code:

select 1 as Tag,Null as parent,'2.0' as 'rss!1!version',Null as
'Link!2!rel',Null as [Channel!3!Title!element], Null as
[Channel!3!Link!element],null as [Channel!3!Description!element],Null as
[Channel!3!language!element], NULL as [Item!4!Title!element], NULL as
[Item!4!Url!element], NULL as [Item!4!PubDate!element]

UNION ALL

select 2,1,Null,'alternate' as 'Link!2!rel',Null as
[Channel!3!Title!element],Null as [Channel!3!Link!element], Null as
[Channel!3!Description!element],Null as [Channel!3!language!element],NULL as
[Item!3!Title!element], NULL as [Item!3!Url!element], NULL as
[Item!3!PubDate!element]

UNION ALL

SELECT 3,2,Null,Null, 'My Website feed channel title' as
[Channel!2!Title!element], 'http://www.dhameliya.blogspot.com' as
[Channel!2!Link!element], 'This is channel description' as
[Channel!2!Description!element], 'en-us' as [Channel!2!language!element], NULL
as [Item!3!Title!element], NULL as [Item!3!Url!element], NULL as
[Item!3!PubDate!element]

UNUNION ALL

SELECT 4, 3,Null,Null,Null,Null,Null,Null, sa.Title, sa.Url, sa.PubDate FROM
myFeeds sa

For XML EXPLICIT

--End of Code

at first site this is very complex code but when you run this without "For XML EXPLICIT" Statements.it is simple a transformation table which looks like :

Transformation Table:



When you look at Tag and Parent Column you can easily judge what actually happening in this query.


Hope will help you.

happy programming.

thanks.