sql - FOR XML PATH and string concatenation

Question

I am trying to create a value that concatenates both hard coded strings and strings created using FOR XML PATH.

SUBSTRING(
(SELECT (', ' + [value]) 
 FROM [values]
 FOR XML PATH( '' )
), 3, 1000) +
' text in between my values ' +
SUBSTRING(
(SELECT (', ' + [otherValue]) 
 FROM [otherValues]
 FOR XML PATH( '' )
), 3, 1000)

So I would expect something like: Value1, Value2, Value3 text in between my values OtherValue1, OtherValue2, OtherValue3, but instead I get a blank string.

If I take out the substrings (including the query inside it) I get the values in between fine, and if I only add one of the substring blocks by itself I get the the list string back. So I can't work out why having concatenation with the substring and FOR XML PATH queries causes it return an empty string.

Answer 1

I think your original attempt is almost there. I do this type of thing all the time using the same FOR XML approach. The solution below solves your exact example and can be easily adapted for other purposes:

DECLARE @delimiter NVARCHAR(10)
SET @delimiter = ', '

declare @values TABLE (
    [value] NVARCHAR(25)
)

declare @otherValues TABLE (
    [otherValue] NVARCHAR(25)
)

INSERT INTO @values VALUES ('Value1')
INSERT INTO @values VALUES ('Value2')
INSERT INTO @values VALUES ('Value3')

INSERT INTO @otherValues VALUES ('OtherValue1')
INSERT INTO @otherValues VALUES ('OtherValue2')
INSERT INTO @otherValues VALUES ('OtherValue3')

SELECT
    STUFF(
        (
            SELECT
                @delimiter + CAST([value] AS NVARCHAR(500)) + '' AS [text()]
            FROM
                @values
            FOR   
                XML PATH('')
        ),
        1,
        LEN(REVERSE(@delimiter)), -- Reverse the delimiter string in case it has trailing spaces; LEN() won't count those
        ''
    ) +
    ' text in between my values ' +
    STUFF(
        (
            SELECT
                @delimiter + CAST([otherValue] AS NVARCHAR(500)) + '' AS [text()]
            FROM
                @otherValues
            FOR   
                XML PATH('')
        ),
        1,
        LEN(REVERSE(@delimiter)), -- Reverse the delimiter string in case it has trailing spaces; LEN() won't count those
        ''
    )