title: T SQL For XML PATH Group By as Attribute or Element
body: <p>I have been working on the T-SQL FOR XML with PATH Mode to create a Hierarchy based on the group by field.
  Below is my query and output. Pls help me with your valuable suggestions. Thank you. Good Day!!! </p>
  
  <pre><code>select e.department_id AS [@DepartmentID],
  d.DEPARTMENT_NAME AS [@DepartmentName],
  e.EMPLOYEE_ID AS [EmployeeInfo/EmployeeID],
  e.FIRST_NAME AS [EmployeeInfo/FirstName],
  e.LAST_NAME AS [EmployeeInfo/LastName]
  from employees e
  JOIN departments d 
  ON e.department_id = d.department_id
  GROUP BY e.department_id,d.DEPARTMENT_NAME,
  e.EMPLOYEE_ID,e.FIRST_NAME,e.LAST_NAME
  FOR XML PATH ('Department'), ROOT ('Departments')
  </code></pre>
  
  <p><strong>Output:</strong></p>
  
  <pre><code> &lt;Departments&gt;
    &lt;Department DepartmentID="10"&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;111&lt;/EmployeeID&gt;
        &lt;FirstName&gt;John&lt;/FirstName&gt;
        &lt;LastName&gt;Chen&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
    &lt;/Department&gt;
    &lt;Department DepartmentID="10"&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;201&lt;/EmployeeID&gt;
        &lt;FirstName&gt;steven&lt;/FirstName&gt;
        &lt;LastName&gt;Whalen&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
    &lt;/Department&gt;
    &lt;Department DepartmentID="30"&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;105&lt;/EmployeeID&gt;
        &lt;FirstName&gt;ANIRUDH&lt;/FirstName&gt;
        &lt;LastName&gt;RAMESH&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
    &lt;/Department&gt;
    &lt;Department DepartmentID="30"&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;115&lt;/EmployeeID&gt;
        &lt;FirstName&gt;Den&lt;/FirstName&gt;
        &lt;LastName&gt;Raphaely&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
    &lt;/Department&gt;
  &lt;Departments&gt;
  </code></pre>
  
  <p><strong>Desired Output is :</strong></p>
  
  <pre><code>&lt;Departments&gt;
    &lt;Department DepartmentID="10"&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;111&lt;/EmployeeID&gt;
        &lt;FirstName&gt;John&lt;/FirstName&gt;
        &lt;LastName&gt;Chen&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;201&lt;/EmployeeID&gt;
        &lt;FirstName&gt;steven&lt;/FirstName&gt;
        &lt;LastName&gt;Whalen&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
    &lt;/Department&gt;
    &lt;Department DepartmentID="30"&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;105&lt;/EmployeeID&gt;
        &lt;FirstName&gt;ANIRUDH&lt;/FirstName&gt;
        &lt;LastName&gt;RAMESH&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
      &lt;EmployeeInfo&gt;
        &lt;EmployeeID&gt;115&lt;/EmployeeID&gt;
        &lt;FirstName&gt;Den&lt;/FirstName&gt;
        &lt;LastName&gt;Raphaely&lt;/LastName&gt;
      &lt;/EmployeeInfo&gt;
    &lt;/Department&gt;
  &lt;Departments&gt;
  </code></pre>