title: Sql XML Path with different children
body: <p>I have done a lot of XML PATH statements, but this one escapes me or might not even be possible with multiple different children.</p>
  
  <p>The end result should look like this </p>
  
  <pre><code>&lt;Process&gt;
  &lt;TaskList&gt;
  &lt;SqlTask Name="Get Report Parameters"&gt;
      &lt;StoredProcName&gt;GetReportParameters&lt;/StoredProcName&gt;
          &lt;ConnectionName&gt;Local&lt;/ConnectionName&gt;
          &lt;DataTableName&gt;DistributionList&lt;/DataTableName&gt;
          &lt;Parameters&gt;
                &lt;Parameter&gt;
              &lt;Name&gt;ReportName&lt;/Name&gt;
              &lt;Value&gt;TheReprot&lt;/Value&gt;
              &lt;Type&gt;String&lt;/Type&gt;
                &lt;/Parameter&gt;
          &lt;/Parameters&gt;
    &lt;/SqlTask&gt;
    &lt;LoopTask Name="Loop Report Creation" ContainerKey="DistributionList"&gt;
    &lt;TaskList&gt;
          &lt;ReportTask Name="Report In Loop"&gt;   
      &lt;/ReportTask&gt;
  &lt;/TaskList&gt;
    &lt;/LoopTask&gt;
    &lt;SqlTask Name="Get Email Addresses"&gt;
      &lt;StoredProcName&gt;GetMailingAddress&lt;/StoredProcName&gt;
          &lt;ConnectionName&gt;Local&lt;/ConnectionName&gt;
          &lt;DataTableName&gt;EmailList&lt;/DataTableName&gt;
  
    &lt;/SqlTask&gt;
    &lt;LoopTask Name="Loop Mail Creation" ContainerKey="EmailList"&gt;
  &lt;TaskList&gt;
          &lt;MailTask Name="Send Email In Loop"&gt;       
          &lt;/MailTask&gt;
  &lt;/TaskList&gt;
    &lt;/LoopTask&gt;
  &lt;/TaskList&gt;
  &lt;/Process&gt;
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>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
  
  
  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'
  )
  
  
  INSERT INTO #TASK 
  (
      ProcessId
  ,    TaskType
  ,    TaskName
  ,    ContainerKey
  ,    ParentTaskId
  )
  VALUES 
  (
  0
  ,    'LoopTask'
  ,    'Loop Report Creation'
  ,    'DistributionList'
  ,    NULL
  )
  
  SET @ParentTaskId = @@IDENTITY
  
  
  INSERT INTO #TASK 
  (
      ProcessId
  ,    TaskType
  ,    TaskName
  ,    ContainerKey
  ,    ParentTaskId
  )
  VALUES 
  (
  0
  ,    'ReportTask'
  ,    'Report In Loop'
  ,    NULL
  ,    @ParentTaskId
  )
  
  
  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'
  )
  
  
  INSERT INTO #TASK 
  (
      ProcessId
  ,    TaskType
  ,    TaskName
  ,    ContainerKey
  ,    ParentTaskId
  )
  VALUES 
  (
  0
  ,    'LoopTask'
  ,    'Loop Mail Creation'
  ,    'EmailList'
  ,    NULL
  )
  
  SET @ParentTaskId = @@IDENTITY
  
  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
  </code></pre>