Sql To XML

[mbde]

Hi, I have done a lot of XML PATH statements, but this one escapes me or might not even be possible with multiple different children.

The end result should look like this
<Process>
<TaskList>
<SqlTask Name="Get Report Parameters">
<StoredProcName>GetReportParameters</StoredProcName>
<ConnectionName>Local</ConnectionName>
<DataTableName>DistributionList</DataTableName>
<Parameters>
<Parameter>
<Name>ReportName</Name>
<Value>TheReprot</Value>
<Type>String</Type>
</Parameter>
</Parameters>
</SqlTask>
<LoopTask Name="Loop Report Creation" ContainerKey="DistributionList">
<TaskList>
<ReportTask Name="Report In Loop">
</ReportTask>
</TaskList>
</LoopTask>
<SqlTask Name="Get Email Addresses">
<StoredProcName>GetMailingAddress</StoredProcName>
<ConnectionName>Local</ConnectionName>
<DataTableName>EmailList</DataTableName>

</SqlTask>
<LoopTask Name="Loop Mail Creation" ContainerKey="EmailList">
<TaskList>
<MailTask Name="Send Email In Loop">
</MailTask>
</TaskList>
</LoopTask>
</TaskList>
</Process>


Below is some test table and data I have so far. The question really is how can I display different child nodes under the same root. And can I derive a tag name from a column value?

CREATE TABLE #TASK (
	TaskId INT IDENTITY(1,1)
,	ProcessId INT
,	TaskType VARCHAR(255)
,	TaskName VARCHAR(255)
,	ContainerKey VARCHAR(255)
,	ParentTaskId INT
)

CREATE TABLE #TASK_PARAMETERS 

(
	TaskId INT
,	Name VARCHAR(255)
,	Value VARCHAR(MAX)
,	[Type] VARCHAR(128)
)

CREATE TABLE #TASK_DETAILS
(
	TaskId INT
,	DetailName VARCHAR(255)
,	DetailValue VARCHAR(MAX)
)

DECLARE @TaskId AS INT
DECLARE @ParentTaskId AS INT
-- Sql

INSERT INTO #TASK 
(
	ProcessId
,	TaskType
,	TaskName
,	ContainerKey
,	ParentTaskId
)
VALUES 
(
	0
,	'SqlTask'
,	'Get Report Parameters'
,	NULL
,	NULL
)

SET @TaskId = @@IDENTITY

INSERT INTO #TASK_DETAILS
(
	TaskId
,	DetailName
,	DetailValue
)
VALUES 
(
	@TaskId
,	'StoredProceName'
,	'GetReportParamters'
)

INSERT INTO #TASK_DETAILS
(
	TaskId
,	DetailName
,	DetailValue
)
VALUES 
(
	@TaskId
,	'ConnectionName'
,	'Local'
)

INSERT INTO #TASK_DETAILS
(
	TaskId
,	DetailName
,	DetailValue
)
VALUES 
(
	@TaskId
,	'DataTableName'
,	'DistributionList'
)

INSERT INTO #TASK_PARAMETERS 

(
	TaskId
,	Name
,	Value
,	[Type]
)
VALUES 
(
	@TaskId
,	'ReportName'
,	'TheReprot'
,   'String'
)

-- Loop
INSERT INTO #TASK 
(
	ProcessId
,	TaskType
,	TaskName
,	ContainerKey
,	ParentTaskId
)
VALUES 
(
	0
,	'LoopTask'
,	'Loop Report Creation'
,	'DistributionList'
,	NULL
)

SET @ParentTaskId = @@IDENTITY

-- Report
INSERT INTO #TASK 
(
	ProcessId
,	TaskType
,	TaskName
,	ContainerKey
,	ParentTaskId
)
VALUES 
(
	0
,	'ReportTask'
,	'Report In Loop'
,	NULL
,	@ParentTaskId
)

--Sql
INSERT INTO #TASK 
(
	ProcessId
,	TaskType
,	TaskName
,	ContainerKey
,	ParentTaskId
)
VALUES 
(
	0
,	'SqlTask'
,	'Get Email Addresses'
,	NULL
,	NULL
)

SET @TaskId = @@IDENTITY

INSERT INTO #TASK_DETAILS
(
	TaskId
,	DetailName
,	DetailValue
)
VALUES 
(
	@TaskId
,	'StoredProceName'
,	'GetMailingAddress'
)

INSERT INTO #TASK_DETAILS
(
	TaskId
,	DetailName
,	DetailValue
)
VALUES 
(
	@TaskId
,	'ConnectionName'
,	'Local'
)

INSERT INTO #TASK_DETAILS
(
	TaskId
,	DetailName
,	DetailValue
)
VALUES 
(
	@TaskId
,	'DataTableName'
,	'EmailList'
)


-- Loop
INSERT INTO #TASK 
(
	ProcessId
,	TaskType
,	TaskName
,	ContainerKey
,	ParentTaskId
)
VALUES 
(
	0
,	'LoopTask'
,	'Loop Mail Creation'
,	'EmailList'
,	NULL
)

SET @ParentTaskId = @@IDENTITY

-- Mail
INSERT INTO #TASK 
(
	ProcessId
,	TaskType
,	TaskName
,	ContainerKey
,	ParentTaskId
)
VALUES 
(
	0
,	'MailTask'
,	'Send Email In Loop'
,	NULL
,	@ParentTaskId
)


SELECT *
FROM #TASK

SELECT *
FROM #TASK_PARAMETERS 

SELECT *
FROM  #TASK_DETAILS