Fun with T-SQL

Convert parent-child hierarchy table to XML with indentation

Created using recursive CTEs. Run in SQL 2008.

See other SQL fractals

T-SQL Source

with tree(id, parentid, name) as 
(
	select ResourceTypeGuid, BaseResourceTypeGuid, Name
		from ResourceTypeHierarchy r
		join Item i on r.ResourceTypeGuid = i.Guid
		where Depth=1
	union all
	select Guid, Null, Name from Item
		where Guid in ( select BaseResourceTypeGuid from ResourceTypeHierarchy where Depth=1 )
		and Guid not in ( select ResourceTypeGuid from ResourceTypeHierarchy where Depth=1 )
),
paths(id, path, indent) as
(
	select id, cast(id as nvarchar(max)), cast('' as nvarchar(max))
		from tree where parentid is null
	union all
	select t.id, p.path + '.' + cast(t.id as nvarchar(max)), p.indent + N'  '
		from tree t
		join paths p on p.id = t.parentid
),
tags(id, path, format, indent) as
(
	select id, path, 0, indent from paths p where exists ( select 1 from tree t where t.parentid = p.id )
	union all
	select id, path + '~', 1, indent from paths p where exists ( select 1 from tree t where t.parentid = p.id )
	union all
	select id, path, 2, indent from paths p where not exists ( select 1 from tree t where t.parentid = p.id )
),
strings(tag, n) as
(
	select 
		indent +
		case
			when tags.format = 0 then N'<' + t.name + N'>' + char(13)
			when tags.format = 1 then N'</' + t.name + N'>' + char(13)
			when tags.format = 2 then N'<' + t.name + N'/>' + char(13)
		end,
		row_number() over (order by path)
	from tags
		join tree t on tags.id = t.id
),
builder(s, depth) as 
(
	select cast('' as nvarchar(max)), cast(max(n) as int)
		from strings
	union all
	select cast(d.tag as nvarchar(max)) + b.s, b.depth-1
		from builder b
		join strings d on b.depth = d.n	
)
select s xmlResult from builder where depth = 0
	

See other SQL fractals