I have read that there is a length issue using FOR XML PATH but I have not been able to reproduce it (and maybe it was related to the Column Display Length Limit in SSMS ...)
I typically see this posted as
SELECT P.Col1, P.Col2, ... , STUFF( ( SELECT ',' + MyValue FROM MyChildTable AS C WHERE C.MyKey = P.MyKey ORDER BY MyValue FOR XML PATH('') ) , 1, 1, '') AS MyValueList FROM MyParentTable AS P
however that will "escape" XML charracters ">", "<", "&". The solution to that is to change
FOR XML PATH('') ) to: FOR XML PATH(''), TYPE ).value('.', 'varchar(max)') -- NOTE: "'value" is case sensitive!!
I've tried all sorts of tests and I cannot get this to fail on length - I concatenated 1,000,000 rows of 7-character data and both methods worked fine
If using a space in the delimiter, i.e. ", " instead of just ",", then change:
, 1, 1, '') AS MyValueList to: , 1, 2, '') AS MyValueList
FOR XML PATH will omit any value which is NULL. To preserve NULL values change them to a blank string, or "(blank)" or similar. Change:
SELECT ',' + MyValue to: SELECT ',' + COALESCE(MyValue, '(blank)')
blanks are included though, and if they are not wanted (and NULL is also not wanted) then change
SELECT ',' + MyValue to: SELECT ',' + NullIf(MyValue, '')
or filter them out in the WHERE clause
I did wonder if it might be possible to use an XML directive to chop-off the leading comma (instead of using STUFF), any ideas?
Any other thoughts anyone has please?