Fun with T-SQL

Convert parent-child hierarchy table to XML in T-SQL

Created using recursive CTEs. Run in SQL 2008.

See other SQL fractals

T-SQL Source

with tree(id, parentid, name) as 
(
	select 1, null, 'Resource'
	union select 2, 1, 'Computer'
	union select 3, 2, 'VM'
	union select 4, 1, 'User'
	union select 5, 4, 'Employee'
	union select 6, 5, 'Manager'
	union select 7, 1, 'Department'
),
paths(id, path) as
(
	select id, cast(id as nvarchar(max))
		from tree where parentid is null
	union all
	select t.id, p.path + '.' + cast(t.id as nvarchar(max))
		from tree t
		join paths p on p.id = t.parentid
),
tags(id, path, format) as
(
	select id, path, 0 from paths p where exists ( select 1 from tree t where t.parentid = p.id )
	union all
	select id, path + '~', 1 from paths p where exists ( select 1 from tree t where t.parentid = p.id )
	union all
	select id, path, 2 from paths p where not exists ( select 1 from tree t where t.parentid = p.id )
),
strings(tag, n) as
(
	select 
		case
			when tags.format = 0 then '<' + t.name + '>'
			when tags.format = 1 then '</' + t.name + '>'
			when tags.format = 2 then '<' + t.name + '/>'
		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 d.tag + s.s, s.depth-1
		from builder s
		join strings d on s.depth = d.n	
)
select s from builder where depth = 0
	

See other SQL fractals