truncate table SiteMap;
declare @tempResults as Table(Sequence int NOT NULL IDENTITY (1, 1),
	ID		[uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
	[Title]       	[varchar] (512),
	[Description] 	[varchar] (512),
	[Url]         	[varchar] (512),
   	[Roles]       	[varchar] (512),
	[AdditionalProperties] [varchar] (max),
	[Parent]      [uniqueidentifier],
	[ParentUrl]   [varchar](512))

declare @xml xml;
set @xml = '<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>'
insert into @tempResults(url, title, description, AdditionalProperties, parentUrl)
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);

declare @tempResults2 as Table(Sequence int NOT NULL IDENTITY (1, 1),
	ID			  [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
    [Url]         [varchar] (512));
insert into @tempResults2 (id, url)
select id, url
from @tempResults;

update @tempResults
set a.parent = b.id
from @tempResults as a inner join @tempResults2 as b on a.parentUrl = b.url;

INSERT INTO [SiteMap]
           ([id]
           ,[Title]
           ,[Description]
           ,[Url]
           ,[Roles]
           ,[Parent]
           ,[Sequence]
		   ,[AdditionalProperties])
SELECT	 ID     
		,Title
        ,Description
        ,Url
		,Roles
		,Parent
		,Sequence
		,AdditionalProperties
FROM @tempResults
select * from SiteMap