CSV to XML in SQL Sever 2005

1)Split a delimited string
CREATE TABLE [dbo].[sample_xml](
[id] [int] NULL,
[name] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

INSERT INTO sample_xml (id,name) VALUES(1,'Mallik,Nishant,Kumar')
INSERT INTO sample_xml (id,name) VALUES(2,'Anil,Inder,Kalyan')


WITH cte AS (
SELECT
id,
CAST('' + REPLACE(name, ',', '') + '' AS XML) AS NAMES
FROM sample_xml
)
SELECT
id,
x.i.value('.', 'VARCHAR(10)') AS NAME
FROM cte
CROSS APPLY NAMES.nodes('//i') x(i)
FOR XML AUTO

2)Generate a delimited string from a set
DECLARE @companies Table(
CompanyID INT,
CompanyCode int
)

insert into @companies(CompanyID, CompanyCode) values(1,1)
insert into @companies(CompanyID, CompanyCode) values(1,2)
insert into @companies(CompanyID, CompanyCode) values(2,1)
insert into @companies(CompanyID, CompanyCode) values(2,2)
insert into @companies(CompanyID, CompanyCode) values(2,3)
insert into @companies(CompanyID, CompanyCode) values(2,4)
insert into @companies(CompanyID, CompanyCode) values(3,1)
insert into @companies(CompanyID, CompanyCode) values(3,2)

SELECT * FROM @companies
/*
CompanyID CompanyCode
----------- -----------
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
*/

This is the result that we need.

/*
CompanyID CompanyString
----------- -------------------------
1 1,2
2 1,2,3,4
3 1,2
*/

SELECT CompanyID,
REPLACE((SELECT
CompanyCode AS 'data()'
FROM @companies c2
WHERE c2.CompanyID = c1.CompanyID
FOR XML PATH('')), ' ', ',') AS CompanyString
FROM @companies c1
GROUP BY CompanyID