Dynamically Populating a Microsoft Internet Explorer TreeView Web Control from a Database using SQL Server 2000 & XML Explicit

create procedure spReflexiveNodesText
	as
select
	1 as Tag,
	0 as Parent,
	null as [TREENODES!1!text],
	null as [treenode!2!text],
	null as [treenode!2!NavigateUrl],
	null as [treenode!3!text],
	null as [treenode!3!NavigateUrl],
	null as [treenode!4!text],
	null as [treenode!4!NavigateUrl]
union 
	all
select
	2 as Tag,
	1 as Parent,
	null,
	fldDiagnosisLabel as [treenode!2!text],
	'Javascript:FillBox("' + cast(fldDiagnosisId as varchar(10)) + '")'
		 as [treenode!2!NavigateUrl],
	null as [treenode!3!text],
	null as [treenode!3!NavigateUrl],
	null as [treenode!4!text],
	null as [treenode!4!NavigateUrl]
from
	tblDiagnosis
where
	fldTierNumber = 1
union
	all
select
	3 as Tag,
	2 as Parent,
	null,
	t1.fldDiagnosisLabel,
	'Javascript:FillBox("' + cast(t1.fldDiagnosisId as varchar(10)) + '")',
	t2.fldDiagnosisLabel as [treenode!3!text],
	'Javascript:FillBox("' + cast(t2.fldDiagnosisId as varchar(10)) + '")'
	as [treenode!3!NavigateUrl],
	null,
	null
from
	tblDiagnosis as t1
inner join
	tblDiagnosis as t2
on
	t1.fldDiagnosisId = t2.fldDiagnosisParentId
where
	t1.fldTierNumber = 1
and
	t2.fldTierNumber = 2
union
	all
select
	4 as Tag,
	3 as Parent,
	null,
	t1.fldDiagnosisLabel,
	'Javascript:FillBox("' + cast(t1.fldDiagnosisId as varchar(10)) + '")',
	t2.fldDiagnosisLabel,
	'Javascript:FillBox("' + cast(t2.fldDiagnosisId as varchar(10)) + '")',
	t3.fldDiagnosisLabel as [treenode!4!text],
	'Javascript:FillBox("' + cast(t3.fldDiagnosisId as varchar(10)) + '")'
		 as [treenode!4!NavigateUrl]
from
	tblDiagnosis as t1
inner join
	tblDiagnosis as t2
on
	t1.fldDiagnosisId = t2.fldDiagnosisParentId
inner join
	tblDiagnosis as t3
on
	t2.fldDiagnosisId = t3.fldDiagnosisParentId
where
	t1.fldTierNumber = 1
and
	t2.fldTierNumber = 2
and
	t3.fldTierNumber = 3
order by
	[TREENODES!1!text],
	[treenode!2!text],
	[treenode!3!text],
	[treenode!4!text]
for 
	xml explicit