Blog For .NET

As part of a larger project, my recent work has involved migrating Asp.Net sitemaps from Xml to MS SQL in order to use a modified version of the SqlSiteMapProvider. 
Using the xml processing functions new in MS SQL 2005, sitemap files can be imported ease.

A “regular” sitemap file is going to look like this:

<siteMap>
  <siteMapNode title="Home" description="Home" url="~/default.aspx" SomeProperty="someValue">
    <siteMapNode title="Products" description="Our products"
      url="~/Products.aspx"  SomeProperty="someValue"  SomeOtherProperty="someOtherValue">
      <siteMapNode title="Hardware" description="Hardware choices"
        url="~/Hardware.aspx" SomeThirdProperty="someThirdValue"/>
      <siteMapNode title="Software" description="Software choices"
        url="~/Software.aspx" />
    </siteMapNode>
    <siteMapNode title="Services" description="Services we offer"
        url="~/Services.aspx">
        <siteMapNode title="Training" description="Training classes"
          url="~/Training.aspx" />
        <siteMapNode title="Consulting" description="Consulting services"
          url="~/Consulting.aspx" />
        <siteMapNode title="Support" description="Supports plans"
          url="~/Support.aspx" />
    </siteMapNode>
  </siteMapNode>
</siteMap>

using the script here we can populate a table (created with this script).

Note: siteMap nodes can contain custom additional attributes. This script inserts these attributes into the AdditionalProperties column as xml elements:

    <siteMapNode title="Products" description="Our products"
      url="~/Products.aspx"  SomeProperty="someValue"  SomeOtherProperty="someOtherValue">

becomes

<row name="SomeProperty" value="someValue"/>
<row name="SomeOtherProperty" value="someOtherValue"/>

The bulk of the work is down in this query:

select T.c.value('@url', 'nvarchar(255)') as url
, T.c.value('@title', 'nvarchar(255)') as title
, isnull(T.c.value('@description', 'nvarchar(255)'), '') as description
,
	(select U.c.value('local-name(.)', 'varchar(255)') as name , U.c.value('.', 'varchar(255)') as value
	from T.c.nodes('@*[local-name(.) != "url" and local-name(.) != "title" and local-name(.) != "description"]') U(c)
	FOR XML RAW
)
as AdditionalProperties
,T.c.value('../@url', 'nvarchar(255)') as parentUrl
from @xml.nodes('/siteMap//siteMapNode') T(c);

First we parse the xml variable into “rows” using the nodes() function to query to for all the “siteMapNode” elements.
To build the “AdditionalProperties” value for an element node, we do the following:
Parse the element attributes into rows using the XPath query “@*”.
Filter out attribute rows with already defined names (url, title, etc.)
Combine the result set of attributes again into a single entry using the “FOR XML RAW” statement.

Note: this uses the MS SQL functions for untyped XML. If the config has an xmlns attribute, it must be removed before running the script.
To verify the results, run the following query and compare the results to the source config file:

with TMP ([id]
      ,[Title]
      ,[Description]
      ,[Url]
      ,[Roles]
      ,[Parent]
      ,[Sequence]
      ,[AdditionalProperties]
	  ,level)
as (
	SELECT [id]
      ,[Title]
      ,[Description]
      ,[Url]
      ,[Roles]
      ,[Parent]
      ,[Sequence]
      ,[AdditionalProperties]
	  ,0 as level
  FROM [SiteMap] as a
  where parent is null
  union all
	SELECT a.[id]
      ,a.[Title]
      ,a.[Description]
      ,a.[Url]
      ,a.[Roles]
      ,a.[Parent]
      ,a.[Sequence]
      ,a.[AdditionalProperties]
	  ,TMP.level + 1 as level
  FROM [SiteMap] as a
  inner join TMP on a.parent = TMP.id
)
select * from Tmp
order by level, sequence

No Comments yet »