Generating and Splitting a Delimited String Column

/* create table @DeveloperProject */
DECLARE @DeveloperProject Table(    
    DeveoperID INT,    
    ProjectCode  VARCHAR(20)
) 

/* insert dummy data for the table @DeveloperProject */
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'REPT_MOD')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(3,'REPT_MOD')

/* creating delimited project codes */
SELECT DeveoperID,
    REPLACE((SELECT 
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

SELECT DeveoperID,
    SUBSTRING((SELECT 
        ',' + ProjectCode
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), 2, 8000) AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

/* explanation data() function; project code with code attribute */
SELECT ProjectCode 
FROM @DeveloperProject c2      
FOR XML PATH('')

/* project code without attribute */
SELECT ProjectCode AS 'data()'
FROM @DeveloperProject c2      
FOR XML PATH('')


/* create table to store comma seperated project codes */
DECLARE @DeveloperProjectCSV Table(    
    DeveoperID INT,    
    ProjectCodes  VARCHAR(MAX)
)

/* insert data to the table @DeveloperProjectCSV */
INSERT INTO @DeveloperProjectCSV(DeveoperID,ProjectCodes)
SELECT DeveoperID,
    REPLACE((SELECT 
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2 
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

SELECT * FROM @DeveloperProjectCSV

/* splitting the comma seperated project list */
;WITH cte AS (
    SELECT 
        DeveoperID,
        CAST('<r>' + REPLACE(ProjectCodes, ',', '</r><r>') + '</r>' AS XML) AS ProjectCodes
    FROM @DeveloperProjectCSV
)
SELECT 
    DeveoperID,
    xTable.xColumn.value('.', 'VARCHAR(MAX)') AS ProjectCode
FROM cte
CROSS APPLY ProjectCodes.nodes('//r') AS xTable(xColumn)