DNN Sitemap (Hack)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T_TabsForXmlSitemapOverrides](
    [TabId] [INT] NOT NULL,
    [URL] [VARCHAR](1024) NULL,
    [hreflang] [VARCHAR](10) NULL,
 CONSTRAINT [PK_T_TabsForXmlSitemapOverrides] PRIMARY KEY CLUSTERED
(
    [TabId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE VIEW [dbo].[V_TabsForXmlSitemap]
AS
SELECT DISTINCT
                         TOP (100) PERCENT ISNULL(dbo.T_TabsForXmlSitemapOverrides.URL, 'https://www.mysite.com' + REPLACE(SUBSTRING(TabPath, 0, LEN(TabPath) - CHARINDEX('//', REVERSE(TabPath))), '//', '/') + '/' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TabName,':','-'),'&','-'),' ','-'),'/','-'), '(', ''), ')', ''), '-', '<>'), '><', ''), '<>', '-'))
                         AS URL, dbo.T_TabsForXmlSitemapOverrides.hreflang
FROM            dbo.Tabs INNER JOIN
                         dbo.TabPermission ON dbo.Tabs.TabID = dbo.TabPermission.TabID LEFT OUTER JOIN
                         dbo.T_TabsForXmlSitemapOverrides ON dbo.Tabs.TabID = dbo.T_TabsForXmlSitemapOverrides.TabId
WHERE        (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.StartDate IS NULL) AND (dbo.Tabs.EndDate IS NULL) AND (dbo.T_TabsForXmlSitemapOverrides.TabId IS NULL) AND (dbo.Tabs.IsDeleted = 0) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.StartDate IS NULL) AND (dbo.Tabs.EndDate IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (dbo.T_TabsForXmlSitemapOverrides.URL IS NOT NULL) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.EndDate IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (dbo.T_TabsForXmlSitemapOverrides.URL IS NOT NULL) AND (GETDATE() >= dbo.Tabs.StartDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.StartDate IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (dbo.T_TabsForXmlSitemapOverrides.URL IS NOT NULL) AND (GETDATE() <= dbo.Tabs.EndDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.IsDeleted = 0) AND (dbo.T_TabsForXmlSitemapOverrides.URL IS NOT NULL) AND (GETDATE() >= dbo.Tabs.StartDate) AND (GETDATE() <= dbo.Tabs.EndDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.IsDeleted = 0) AND (dbo.T_TabsForXmlSitemapOverrides.URL IS NOT NULL) AND (GETDATE() >= dbo.Tabs.StartDate) AND (GETDATE() <= dbo.Tabs.EndDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.EndDate IS NULL) AND (dbo.T_TabsForXmlSitemapOverrides.TabId IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (GETDATE() >= dbo.Tabs.StartDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.Tabs.StartDate IS NULL) AND (dbo.T_TabsForXmlSitemapOverrides.TabId IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (GETDATE() <= dbo.Tabs.EndDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.T_TabsForXmlSitemapOverrides.TabId IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (GETDATE() >= dbo.Tabs.StartDate) AND (GETDATE() <= dbo.Tabs.EndDate) OR
                         (dbo.TabPermission.PermissionID = 3) AND (dbo.TabPermission.RoleID = - 1 OR
                         dbo.TabPermission.RoleID = - 3) AND (dbo.T_TabsForXmlSitemapOverrides.TabId IS NULL) AND (dbo.Tabs.IsDeleted = 0) AND (GETDATE() >= dbo.Tabs.StartDate) AND (GETDATE() <= dbo.Tabs.EndDate)
/* You could add more queries with UNION here, for example to add sitemap links to your store. Just make sure it returns the same columns */
GO

CREATE PROC [dbo].[SP_ExportSitemap] AS SET nocount ON;  WITH XMLNAMESPACES ('http://www.w3.org/1999/xhtml' AS xhtml, DEFAULT 'http://www.sitemaps.org/schemas/sitemap/0.9')  SELECT REPLACE(REPLACE((SELECT [URL] AS 'loc', IIF(hreflang IS NULL, (SELECT '<xhtml:link rel='+CHAR(34)+'alternate'+CHAR(34)+' hreflang='+CHAR(34)+'en'+CHAR(34)+' href='+CHAR(34)+''+URL+''+CHAR(34)+'/><xhtml:link rel='+CHAR(34)+'alternate'+CHAR(34)+' hreflang='+CHAR(34)+'de'+CHAR(34)+' href='+CHAR(34)+''+REPLACE(URL, 'https://www.mysite.com', 'https://www.mysite.com/de')+CHAR(34)+'/>' AS lang), (SELECT '<xhtml:link rel='+CHAR(34)+'alternate'+CHAR(34)+' hreflang='+CHAR(34)+''+hreflang+''+CHAR(34)+' href='+CHAR(34)+''+URL+''+CHAR(34)+'/>' AS lang)) FROM  [dbo].[V_TabsForXmlSitemap] FOR XML PATH('url'), ELEMENTS, ROOT('urlset')), '&lt;', '<'), '&gt;', '>')
GO

/* You can call this stored procedure from the command line. This is an example of how we call it and convert it to UTF-8:
@echo off
set iconvdir=C:\Program Files (x86)\Iconv
set destination=d:\inetpub\wwwroot
echo Export Sitemap.Cmd...
echo ===============================================================================
echo If you continue, this batch will update the sitemap.xml file.
pause
echo Exporting sitemap to %temp%\output.xml...
bcp "exec SP_ExportSitemap" queryout %temp%\output.xml -S %COMPUTERNAME% -d SysManage -T -c
echo Converting sitemap from ANSI to UTF-8...
"%ICONVDIR%\iconv" -f windows-1252 -t UTF-8 %temp%\output.xml >%temp%\sitemap.xml
echo Copying sitemap to destination...
copy %temp%\sitemap.xml %destination%\sitemap.xml
echo Deleting temp file...
del %temp%\output.xml
del %temp%\sitemap.xml
pause
/*