'How can I specify the character to wrap my concatenated string in when using FOR XML PATH

I am trying to output a query in a format that can be serialized into JSON on the client and am concatenating multiple rows into a single comma separated string with the intention of serializing it to an array on the client. Currently my code is resulting in the following:

"'A', 'B', 'C'"

But what I want is

['A', 'B', 'C']

My SQL currently is

DECLARE @json NVARCHAR(MAX)

SET @json = (SELECT 
    STUFF((
     SELECT ',''' + SomeStringField + ''''
        FROM SomeTable
        FOR XML PATH('')
     ), 1, 1, '') AS csv
FOR JSON PATH, ROOT('data')
)

SELECT value FROM OPENJSON(@json, '$.data');

Is there a way I can control the wrapping characters or a better way to do it generally?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source