How do I generate an SQL Query to form XML output

Question

I am trying to generate a XML output as below:

<Employees>
    <EmployeeID>0025907E9BB4</EmployeeID>
    <EmpDateJoin>2015-05-18 01:58:44</EmpDateJoin>
    <EmpRegId>89-16036-1267</EmpRegId>
    <ProjectDetails> 
            Project Name: SVS-DC1 | Project Last Deployed: 2012-03-20 01:48:43 | ErrorDesc: Not Applicable
    </ProjectDetails>
    <ProjectDetails>
        Project Name: ADP-SERVER | Project Last Deployed: 2015-05-18 01:57:43 | ErrorDesc: backup failed due to low memory
    </ProjectDetails>
 </Employees>

But my SQL output is JOINS of multiple tables which return output as

EmployeeID      EmpDateJoin             EmpRegId        ProjectName         LastDeployedDate    ErrorDesc
0025907E9BB4    2015-05-18 01:58:44     89-16036-1267   SVS-DC1             2012-03-20 01:48:43 Not Applicable
0025907E9BB4    2015-05-21 01:48:44     89-16036-1267   ADP-SERVER          2015-05-18 01:57:43 backup failed due to low memory

Just want to know how can I separate distinct values in parent tags and resource values in Project Details tags

I have tried achieving this but was able to generated only below output:

 <Employees>
     <ProjectDetails>
        EmployeeID: 0025907E9BB4 | EmpDateJoin: 2015-05-18 01:58:44 | EmpRegId: 89-16036-1267 | Project Name: SVS-DC1 | Project Last Deployed: 2012-03-20 01:48:43 | ErrorDesc: Not Applicable
    </ProjectDetails>
     <ProjectDetails>
        EmployeeID: 0025907E9BB4 | EmpDateJoin: 2015-05-21 01:48:44 | EmpRegId: 89-16036-1267 | Project Name: ADP-SERVER | Project Last Deployed: 2015-05-18 01:57:43 | ErrorDesc: backup failed due to low memory
    </ProjectDetails>
 </Employees>

SQL Query which is used to achieve the desired output:

DECLARE @XML VARCHAR(MAX)  
 SET @XML =
                (
                SELECT 'EmployeeID: ' + [EmpNo] , ' | EmpDateJoin: ' +  convert(nvarchar(MAX),[EmpDateJoin], 120), ' | EmpRegId: ' + [EmpRegId], 
                ' | Project Name: '+[ProjectName],  ' | Project Last Deployed: ' + convert(nvarchar(MAX), [LastDepDate], 120),  ' | ErrorDesc: ' + [ErrorDesc]                  FROM 
                FROM 
                    (
                    SELECT EmpNo, ProjectName,EmpDateJoin,EmpRegId, LastDepDate, ErrorDesc
                    FROM 
                    (
                        SELECT DAT.EmpNo AS EmpNo, RMC.ProjectName AS ProjectName, 
                        AL.[EmpDateJoin] AS [EmpDateJoin],DAT.[EmpRegId] AS [EmpRegId],
                        AL.[LastDepDate] as [LastDepDate], AL.ErrDescratDevice AS ErrorDesc,
                        CASE 
                            WHEN 
                            (CONS.ParentRegId IS NULL AND dbo.USF_Vault_ER_BackupPerDay(AGT.AgentID,Appliancedt) > 10 AND P.AgentUniqueID IS NOT NULL)  
                            OR
                            (AL.LastBckStatus <> 'SUCCESS' AND CONS.ParentRegId IS NULL AND dbo.USF_Vault_ER_BackupPerDay(AGT.AgentID,AD.Appliancedt) < 11)
                            OR
                            ((dbo.USF_AgentFailureBackupCount(AGT.AgentID) % 2 = 0 AND dbo.USF_AgentFailureBackupCount(AGT.AgentID) > 2 AND dbo.USF_Vault_ER_BackupPerDay(AGT.AgentID,AD.Appliancedt) > 10)
                                OR
                            (AL.LastBckStatus <> 'SUCCESS' AND dbo.USF_Vault_ER_BackupPerDay(AGT.AgentID,AD.Appliancedt) < 11))
                                AND DATEDIFF(MI,'01/01/1900',LBS.AppliancedateTime) > DATEDIFF(MI,'01/01/1900',CONS.RefDatetime) AND CONS.ParentRegId IS NOT NULL 
                        THEN 'E'      
                        ELSE 'G' END AS ProcessStatus
                        FROM Employees DAT WITH(NOLOCK) 
                        INNER JOIN EmployeeRefDep AGT WITH(NOLOCK) ON DAT.OrderID = AGT.OrderID AND AGT.[Status] = 'success'
                        INNER JOIN MstSKU MK  WITH(NOLOCK) ON AGT.ModelID=MK.ModelID AND SKUType = @SkuType
                        INNER JOIN EmpReg RM WITH(NOLOCK) ON DAT.Regid = RM.RegId AND RM.RegId = @InRegid 
                        INNER JOIN EmpReg RMC WITH(NOLOCK) ON AGT.Regid = RMC.RegId 
                        INNER JOIN MstMember MM WITH(NOLOCK) ON RM.MemberID = MM.MemberId AND MM.MemberId = @InMemberId
                        INNER JOIN MstSite MS WITH(NOLOCK) ON RM.SiteId = MS.SiteId AND MS.SiteId = @InSiteid
                        INNER JOIN ProjectLookup AL WITH(NOLOCK) ON AL.AgentUniqueID = AGT.AgentID
                    ) A 
                WHERE ProcessStatus <> 'G'
            )ProtectedMachine
        FOR XML PATH ('ProjectDetails'), ROOT('Employees') 
        )   
        SELECT @XML AS EventDetails

Answer 1

Use this type of query:

;WITH t AS(
     /*Add your query here*/
)
SELECT @XML = CAST( '<Employees>' +
    (SELECT 
        t.EmployeeID,
        MIN(t.EmpDateJoin) AS EmpDateJoin,
        t.EmpRegId
    FROM t
    GROUP BY 
        t.EmployeeID,
        t.EmpRegId
    FOR XML PATH(''))
    +
    (SELECT 
        'Project Name : ' + ProjectName + '| Project Last Deployed: ' + LastDeployedDate + '|  ErrorDesc: ' + ErrorDesc AS ProjectDetails
    FROM 
        t Employees
    FOR XML PATH (''))
    +
    '</Employees>' AS xml)

For this:

<Employees>
  <EmployeeID>0025907E9BB4</EmployeeID>
  <EmpDateJoin>2015-05-18 01:58:44</EmpDateJoin>
  <EmpRegId>89-16036-1267</EmpRegId>
  <ProjectDetails>Project Name : SVS-DC1| Project Last Deployed: 2012-03-20 01:48:43|  ErrorDesc: Not Applicable</ProjectDetails>
  <ProjectDetails>Project Name : ADP-SERVER| Project Last Deployed: 2015-05-18 01:57:43|  ErrorDesc: backup failed due to low memory</ProjectDetails>
</Employees>

Answer 2

Here is the answer:

;WITH Employees AS (
    /*Your query here*/
)

SELECT 
    EmployeeID, 
    MIN(EmpDateJoin) AS 'EmpDateJoin', 
    EmpRegId, 
    CAST(STUFF((    SELECT 
                        ProjectName, 
                        LastDeployedDate, 
                        ErrorDesc 
                    FROM Employees E2 
                    WHERE E1.EmployeeID = E2.EmployeeID 
                          AND E1.EmpRegId = E2.EmpRegId 
                    FOR XML PATH('ProjectDetails')
                ), 1, 0, '') AS XML)
FROM Employees E1
GROUP BY EmployeeID, EmpRegId
FOR XML PATH('Employee'), ROOT('Employees'), TYPE

Here is the output:

<Employees>
  <Employee>
    <EmployeeID>0025907E9BB4 </EmployeeID>
    <EmpDateJoin>2015-05-18T01:58:44</EmpDateJoin>
    <EmpRegId>89-16036-1267</EmpRegId>
    <ProjectDetails>
      <ProjectName>SVS-DC1</ProjectName>
      <LastDeployedDate>2012-03-20T01:48:43</LastDeployedDate>
      <ErrorDesc>Not Applicable</ErrorDesc>
    </ProjectDetails>
    <ProjectDetails>
      <ProjectName>ADP-SERVER</ProjectName>
      <LastDeployedDate>2015-05-18T01:57:43</LastDeployedDate>
      <ErrorDesc>backup failed due to low memory</ErrorDesc>
    </ProjectDetails>
  </Employee>
</Employees>

Hope this helps.