String Concatenation Using XML Path

/*
Description : String concatenation using XML Path
This will help you to concatenate the reference table column values by using cross apply
*/
DECLARE @Table1 TABLE(CountryID INT,CountryName VARCHAR(50))
DECLARE @Table2 TABLE(StateID INT IDENTITY(1,1)
                                 ,CountryID INT
                                 ,StateName VARCHAR(50))

INSERT INTO @Table1(CountryID,CountryName) VALUES
(1 , 'USA'),
(2 , 'INDIA'),
(3 , 'AUSTRALIA')


INSERT INTO @Table2(CountryID,StateName) VALUES
(1,'Texas'),
(1,'Washington'),
(1,'New Yark'),
(1,'Colorodo'),
(2,'TamilNadu'),
(2,'Mumbai'),
(2,'Delhi'),
(2,'Banglore'),
(3,'Victoria'),
(3,'Tasmania'),
(3,'Queensland'),
(3,'New South Wales'),
(3,'Western Australia')


SELECT Table1.CountryID
,Table1.CountryName
,LEFT(StateNames,LEN(StateNames)- 1) AS StateNames
FROM @Table1 as Table1
CROSS APPLY
( SELECT StateName + ', '
FROM @Table2 AS Table2
WHERE Table2.CountryID = Table1.CountryID
FOR XML PATH('') ) AS State(StateNames)

--Desired Result

--* This Insert Statement will work in 2008 *