Code Snippet
declare @anXmlString varchar(max)
set @anXmlString = ''
;with nestedSiteMap as
( select 1 as Level,
ID,
Title,
Description,
Url,
Roles,
cast(str(id, 11) as varchar(121))
as path
from siteMap
where parent is null
union all
select level + 1,
b.ID,
b.Title,
b.description,
b.url,
b.roles,
cast( a.path + '/' +
str(b.id, 11) as varchar(121)
)
from nestedSiteMap a
join siteMap b
on a.ID = b.parent
and a.level < 3
), seqSiteMap as
( select row_number() over (order by path) as Seq,
Level,
ID,
Title,
Description,
Url,
roles
from nestedSiteMap
), maxSeq as
( select max(seq) as maxSeq from seqSiteMap
), nextSite as
( select -1 + row_number() over(order by path)as Seq,
Level
from nestedSiteMap
union all
select seq,
1
from seqSiteMap a
join maxSeq b
on seq = maxSeq
), siteOutput as
( select a.Seq as Seq1,
1 as Seq2,
a.Level,
b.Level as nextLevel,
a.ID,
a.Title,
a.Description,
a.Url,
a.Roles
from seqSiteMap a
join nextSite b
on a.seq = b.seq
union all
select Seq1,
Seq2 + 1,
Level - 1,
nextLevel,
ID,
Title,
Description,
Url,
Roles
from siteOutput
where Level > nextLevel
)
select @anXmlString = @anXmlString
+ xmlLine + char(13)
from
(
select seq1, seq2,
case when level = 1 and seq2 = 1
then '<test1="' + cast(id as varchar(11))
+ '">' +char(13)
else '' end +
case
when seq2 = 1
then replicate(' ', 2*level) + '<test2="'
+ cast(id as varchar(11)) + '" Title="' +
rtrim(title) + '" Description="' +
rtrim(description) + '" Url="' +
rtrim(url) + '" Roles="' +
rtrim(Roles) + '"' +
case when nextLevel <= Level
then ' />'
else ' >'
end
else '' end +
case when seq2 > 1 and level = 1
then ' </test2>' + char(13) + '</test1>'
when seq2 > 1 and level > 1
then replicate(' ', 2*level) + '</test2>'
else ''
end as xmlLine
from siteOutput
) z
order by seq1, seq2
select @anXmlString as [@anXmlString]
/*
@anXmlString
--------------------------------------------------------------
<test1="100">
<test2="100" Title="Tim" Description="xyz" Url="~/xyz.aspx id=100" Roles="ttt" >
<test2="101" Title="xxx" Description="xxx" Url="~/xyz.aspx id=101" Roles="rrr" >
<test2="106" Title="bbb" Description="bbb" Url="~/xyz.aspx id=106" Roles="ppp" />
</test2>
<test2="102" Title="yyy" Description="yyy" Url="~/xyz.aspx id=102" Roles="qqq" />
<test2="103" Title="zzz" Description="xxx" Url="~/xyz.aspx id=103" Roles="qqq" />
</test2>
</test1>
<test1="104">
<test2="104" Title="aaa" Description="aaa" Url="~/xyz.aspx id=104" Roles="sss" >
<test2="105" Title="bbb" Description="bbb" Url="~/xyz.aspx id=105" Roles="ppp" />
</test2>
</test1>
<test1="110">
<test2="110" Title="Bq1" Description="Bq1" Url="~/xyz.aspx id=110" Roles="aaa" >
<test2="111" Title="Bq2" Description="Bq2" Url="~/xyz.aspx id=111" Roles="aaa" >
<test2="112" Title="Bq3" Description="Bq3" Url="~/xyz.aspx id=106" Roles="aaa" />
</test2>
</test2>
</test1>
(1 row(s) affected)
*/