title: TSQL for XML Path Grouping
body: <p>I'm having issues trying to group / nest my results as shown below.</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p><strong>Required XML output</strong></p>
  
  <pre><code>&lt;WorkflowLog&gt;
    &lt;Process id="Type1"&gt;
      &lt;ProcessInstance id="M11111"&gt;
        &lt;AuditTrailEntry&gt;
          &lt;Data&gt;
            &lt;Attribute name="TypeSubCause"&gt;EMB&lt;/Attribute&gt;
          &lt;/Data&gt;
          &lt;WorkflowModelElement&gt;C_Created&lt;/WorkflowModelElement&gt;
          &lt;EventType&gt;Start&lt;/EventType&gt;
          &lt;TimeStamp&gt;2013-10-02T10:00:33&lt;/TimeStamp&gt;
          &lt;Originator&gt;C_Team&lt;/Originator&gt;
        &lt;/AuditTrailEntry&gt;
        &lt;AuditTrailEntry&gt;
          &lt;Data&gt;
            &lt;Attribute name="TypeSubCause"&gt;EMB&lt;/Attribute&gt;
          &lt;/Data&gt;
          &lt;WorkflowModelElement&gt;ITPSPotential&lt;/WorkflowModelElement&gt;
          &lt;EventType&gt;Start&lt;/EventType&gt;
          &lt;TimeStamp&gt;2013-10-03T09:15:32+10.00&lt;/TimeStamp&gt;
          &lt;Originator&gt;R_Team&lt;/Originator&gt;
        &lt;/AuditTrailEntry&gt;
      &lt;/ProcessInstance&gt;
    &lt;/Process&gt;
  &lt;/WorkflowLog&gt;
  </code></pre>
  
  <p><strong>Actual XML output from my SQL</strong>
  The Process Id and ProcessInstance ID are showing multiple times for the same Id's</p>
  
  <pre><code>&lt;WorkflowLog&gt;
    &lt;Process id="Type1"&gt;
      &lt;ProcessInstance id="M11111"&gt;
        &lt;AuditTrailEntry&gt;
          &lt;Data&gt;
            &lt;Attribute name="TypeSubCause"&gt;EMBt&lt;/Attribute&gt;
          &lt;/Data&gt;
          &lt;WorkflowModelElement&gt;c_Created&lt;/WorkflowModelElement&gt;
          &lt;EventType&gt;Start&lt;/EventType&gt;
          &lt;TimeStamp&gt;2013-10-02T10:00:33+10.00&lt;/TimeStamp&gt;
          &lt;Originator&gt;C_Team&lt;/Originator&gt;
        &lt;/AuditTrailEntry&gt;
      &lt;/ProcessInstance&gt;
    &lt;/Process&gt;
    &lt;Process id="Type1"&gt;
      &lt;ProcessInstance id="M11111"&gt;
        &lt;AuditTrailEntry&gt;
          &lt;Data&gt;
            &lt;Attribute name="TypeSubCause"&gt;EMB&lt;/Attribute&gt;
          &lt;/Data&gt;
          &lt;WorkflowModelElement&gt;ITPSPotential&lt;/WorkflowModelElement&gt;
          &lt;EventType&gt;Start&lt;/EventType&gt;
          &lt;TimeStamp&gt;2013-10-03T09:15:32+10.00&lt;/TimeStamp&gt;
          &lt;Originator&gt;R_Team&lt;/Originator&gt;
        &lt;/AuditTrailEntry&gt;
      &lt;/ProcessInstance&gt;
    &lt;/Process&gt;
  &lt;/WorkflowLog&gt;
  </code></pre>
  
  <p><strong>The SQL generating this is</strong></p>
  
  <pre><code>SELECT  '@id' = m1.TypeCause ,
          ( SELECT    '@id' = m2.MTypeNumber ,
                      ( SELECT    'TypeSubCause' AS [Data/Attribute/@name] ,
                                  m3.[TypeSubCause] AS [Data/Attribute/*] ,
                                  m3.[Subject] AS [WorkflowModelElement] ,
                                  'Start' AS [EventType] ,
                                  m3.[date] AS [TimeStamp] ,
                                  m3.[AssignedGroup] AS [Originator]
                        FROM      #CombinedDataSets M3
                        WHERE     m3.TypeCause = 'C_Only'
                                  AND m2.MTypeNumber = m3.MTypeNumber
                                  AND m2.Subject = m3.Subject
                                  AND m2.date = m3.date
                      FOR
                        XML PATH('AuditTrailEntry') ,
                            TYPE
                      )
            FROM      #CombinedDataSets m2
            WHERE     m2.TypeCause = 'C_Only'
                      AND m1.MTypeNumber = m2.MTypeNumber
                      AND m1.Subject = m2.Subject
                      AND m1.date = m2.date
          FOR
            XML PATH('ProcessInstance') ,
                TYPE
          )
  FROM    #CombinedDataSets m1
  WHERE   m1.TypeCause = 'C_Only'
  ORDER BY m1.TypeCause ,
          m1.MTypeNumber ,
          m1.date
  FOR     XML PATH('Process') ,
              ROOT('WorkflowLog')
  </code></pre>
  
  <p>Added - Example Data</p>
  
  <pre><code>CREATE TABLE #CombinedDataSets
      (
        [MTypeNumber] VARCHAR(10) ,
        subject VARCHAR(25) ,
        [Date] DATETIME ,
        [AssignedGroup] VARCHAR(25) ,
        [TypeCause] VARCHAR(25) ,
        [TypeSubCause] VARCHAR(25)
      )
  
  INSERT  INTO #CombinedDataSets
          ( [MTypeNumber] ,
            [subject] ,
            [Date] ,
            [AssignedGroup] ,
            [TypeCause] ,
            [TypeSubCause] 
          )
          SELECT  'M11111' ,
                  'C_Created' ,
                  '2013-10-02 10:00:33' ,
                  'CA' ,
                  'C_Only' ,
                  'EMB'
          UNION ALL
          SELECT  'M11111' ,
                  'ITPSPotential' ,
                  '2013-10-03 09:15:32' ,
                  'ALLPIC' ,
                  'C_Only' ,
                  'EMB'
  
  SELECT  *
  FROM    #CombinedDataSets
  
  MTypeNumber subject Date    AssignedGroup   TypeCause   TypeSubCause
  M11111  C_Created   2013-10-02 10:00:33.000 CA  C_Only  EMB
  M11111  ITPSPotential   2013-10-03 09:15:32.000 ALLPIC  C_Only  EMB
  </code></pre>