June 24, 2009 at 6:21 am
Hi,
Here's the problem... I would like to create a stored-procedure which create .sitemap file with values from a SQL table.
1. Script to reproduce the SQL table:
CREATE TABLE [dbo].[dimPortalPages_exnd](
[PageID] [int] NULL,
[title] [nvarchar](255) NULL,
[Parent ID] [int] NULL,
[javascript] [nvarchar](255) NULL,
[Report_Owner] [int] NULL,
[Roles] [nvarchar](255) NULL
) ON [PRIMARY]
2. script to produce the .sitemap
ALTER function dbo.GetSubTree(@ID int, @Level int)
RETURNS XML
BEGIN RETURN
(
SELECT
title AS "@title",
url AS "@url",
Roles AS "@roles",
CASE WHEN [Parent ID] IS NOT NULL THEN dbo.GetSubTree(PageID,@Level+1) END
FROM dbo.dimPortalPages_exnd
WHERE [Parent ID]=@ID or (@Level=1 AND @ID=PageID AND PageID <> 0)
ORDER BY PageID
FOR XML PATH('siteMapNode'),TYPE
)
END
GO
CREATE PROC spSelect_SiteMap
AS
BEGIN
WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/AspNet/SiteMap-File-1.0'
)
SELECT 'Reporting Portal' AS "@title",
dbo.GetSubTree(PageID,1)
FROM dbo.dimPortalPages_exnd
WHERE [Parent ID] IS NULL
ORDER BY PageID
FOR XML PATH('siteMapNode'), ROOT('siteMap')
END
3. OUTPUT:
Several problems with output:
1. No xml document header
2. An extra (no needed) xmlns attribute is appearing for certain elements (only one level 1), where I need a single declaration for the whole document...
Thanks in advance,
Dror
July 20, 2009 at 3:00 am
Could you post some sample data or post the entire XML document?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply