Concatenating strings with FOR XML PATH

In SQL Server 2016 and earlier, FOR XML PATH is a common way to concatenate string values into a single string. SQL Server 2017 introduced a simpler way to get the same result with the STRING_AGG() function.

The first example fills a test table with sample strings and concatenates them using FOR XML PATH. It shows two forms of the query, one with explicit text() aliases and one with a shorter expression.

CREATE TABLE dbo.SampleTable
(
    Value VARCHAR(100)
)
GO

INSERT INTO dbo.SampleTable
        ( Value )
    SELECT 'Value1' UNION ALL
    SELECT 'Value2' UNION ALL
    SELECT 'Value3' UNION ALL
    SELECT 'Value4'
GO

SELECT STUFF((SELECT ',' AS "text()", '''' AS "text()", Value AS "text()",  '''' AS "text()"
                 FROM dbo.SampleTable
                 FOR XML PATH(''))
             , 1, 1, '')
GO

-- returns: 'Value1','Value2','Value3','Value4'

SELECT STUFF((SELECT ','  + '''' + Value + '''' FROM dbo.SampleTable FOR XML PATH('')), 1, 1, '')
GO

-- returns: 'Value1','Value2','Value3','Value4'

The next example uses the same approach to combine the city names for each country into a single string.

-- create sample tables
CREATE TABLE dbo.tblCountry
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.tblCity
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    CountryId INT NOT NULL,
    NAME VARCHAR(100) NOT NULL
)    

-- populate data
ALTER TABLE dbo.tblCity ADD CONSTRAINT FK_tblCity_tblCity FOREIGN KEY (CountryId) 
    REFERENCES dbo.tblCity (Id)

INSERT INTO dbo.tblCountry
    VALUES ('Germany'), ('Czech Republic')

INSERT INTO dbo.tblCity
    VALUES (1, 'Bonn'), (1, 'Berlin')

INSERT INTO dbo.tblCity
    VALUES (2, 'Praha'), (2, 'Brno'), (2, 'Ostrava')

-- review data
SELECT *
FROM dbo.tblCountry country
    INNER JOIN dbo.tblCity city ON city.CountryId = country.Id

-- put cities to inline CSV format
SELECT *
FROM dbo.tblCountry country
    CROSS APPLY (SELECT stuff((SELECT ', ' + c.Name 
                 FROM dbo.tblCity c 
                 WHERE c.CountryId = country.Id
                 FOR XML PATH('')), 1,2,'') Cities
                 FROM (SELECT 1 ID) a) city