/**
[PostType]
1 = Entry
2 = Article
3 = Comment
4 = Trackback
**/
DECLARE @BlogID int
DECLARE @AuthorPrefix int
DECLARE @CategoryPrefix int
DECLARE @RootUrl nvarchar(100)
SET @BlogID = 0
SET @AuthorPrefix = 0
SET @CategoryPrefix = 20000
SET @RootUrl = '/blog/'
SELECT @RootUrl As [root-url], GETDATE() As [date-created],
'http://www.w3.org/2001/XMLSchema' As [xmlns:xs],
(SELECT 1 As [Tag], NULL As [Parent], site.Title As [title!1!!cdata] FROM blog_Config site WHERE site.BlogID = @BlogID FOR XML EXPLICIT, TYPE),
(SELECT 1 As [Tag], NULL As [Parent], subtitle.SubTitle As [sub-title!1!!cdata] FROM blog_Config subtitle WHERE subtitle.BlogID = @BlogID FOR XML EXPLICIT, TYPE),
(SELECT BlogID + @AuthorPrefix As [id], LastUpdated As [date-modified], 'true' As approved, Email As [email],
(SELECT 1 As [Tag], NULL As [Parent], Author As [title!1!!cdata] FROM blog_Config authortitle WHERE author.BlogID = authortitle.BlogID FOR XML EXPLICIT, TYPE)
FROM blog_Config As author
FOR XML AUTO, TYPE) As [authors],
(SELECT category.CategoryID + @CategoryPrefix As [id],
(SELECT 1 As [Tag], NULL AS [Parent], Title As [title!1!!cdata] FROM blog_LinkCategories c2
WHERE c2.Active = 1 AND category.CategoryID = c2.CategoryID
FOR XML EXPLICIT, TYPE)
FROM blog_LinkCategories As category
WHERE category.CategoryType = 1
FOR XML AUTO, TYPE) As categories,
(
SELECT post.ID As id, post.DateAdded As [date-created],
post.DateUpdated As [date-modified],
@RootUrl + 'post/' + CAST(post.ID as nvarchar(1000)) + '.aspx' As [post-url],
'true' As approved,
CASE WHEN PostType = 2 THEN 'article' ELSE 'normal' END As [type], 'false' As [hasexcerpt],
(SELECT (vc.WebCount + vc.AggCount) As [views] FROM blog_EntryViewCount As vc WHERE vc.EntryID = post.ID AND vc.BlogID = @BlogID) As [views],
(SELECT 1 As Tag, NULL AS Parent, title.Title As 'title!1!!cdata' FROM blog_Content As title WHERE title.ID = post.ID FOR XML EXPLICIT, TYPE),
(SELECT 1 As Tag, NULL AS Parent, contents.Text As 'content!1!!cdata' FROM blog_Content As contents WHERE contents.ID = post.ID FOR XML EXPLICIT, TYPE),
(SELECT 1 As Tag, NULL AS Parent, title.Title As 'post-name!1!!cdata' FROM blog_Content As title WHERE title.ID = post.ID FOR XML EXPLICIT, TYPE),
ISNULL((SELECT category.CategoryID + @CategoryPrefix As ref FROM blog_Links As category WHERE category.PostID = post.ID FOR XML AUTO, TYPE), (SELECT MIN(CategoryID) + @CategoryPrefix As ref FROM blog_LinkCategories As category FOR XML AUTO)) As categories,
ISNULL((SELECT tag.Title As ref FROM blog_Links As category INNER JOIN blog_LinkCategories As tag ON category.CategoryID = tag.CategoryID WHERE category.PostID = post.ID FOR XML AUTO, TYPE), (SELECT tag.Title As ref FROM blog_LinkCategories As tag WHERE tag.CategoryID IN (SELECT MIN(CategoryID) As ref FROM blog_LinkCategories) FOR XML AUTO)) As tags,
(SELECT comment.ID As id, DateAdded As [date-created], DateUpdated As [date-modified], 'true' As approved, Author As [user-name], Email As [user-email], TitleUrl As [user-url], (SELECT 1 As Tag, NULL AS Parent, title.Title As 'title!1!!cdata' FROM blog_Content As title WHERE title.ID = comment.ID FOR XML EXPLICIT, TYPE), (SELECT 1 As Tag, NULL AS Parent, contents.Text As 'content!1!!cdata' FROM blog_Content As contents WHERE contents.ID = comment.ID FOR XML EXPLICIT, TYPE) FROM blog_Content As comment WHERE comment.ParentID = post.ID AND PostType = 3 FOR XML AUTO, TYPE) As comments,
(SELECT trackback.ID As id, DateAdded As [date-created], DateUpdated As [date-modified], 'true' As approved, TitleUrl As [url], (SELECT 1 As Tag, NULL AS Parent, title.Title As 'title!1!!cdata' FROM blog_Content As title WHERE title.ID = trackback.ID FOR XML EXPLICIT, TYPE) FROM blog_Content As trackback WHERE trackback.ParentID = post.ID AND PostType = 4 FOR XML AUTO, TYPE) As trackbacks,
(SELECT authorid.BlogID + @AuthorPrefix As ref FROM blog_config As authorid WHERE authorid.BlogID = @BlogID FOR XML RAW('author'), TYPE) As authors
FROM blog_Content As post
WHERE (PostType IN (1, 2))
FOR XML AUTO, ROOT('posts'), TYPE
)
FOR XML RAW('blog')