Character length over 130 does not show in column

Question

I have a lot of questions from a survey im using for a pivot table. To collect all the questions to my pivot dynamically im using stuff and for xml path. However it seems like question text > 130 in length is not showing.

And i can select all the columns from my cte Questions, so I know the data is there.

UPDATE: If I select my output, my total length is around 8.000 could it be something about the nvarchar(max) not storing more than 8.000 even though it should be able to store around 2gb?

What am I doing wrong?

SELECT QuestionList = cast(STUFF((
                SELECT ',' + QUOTENAME(cast(question AS NVARCHAR(max)))
                FROM questions
                ORDER BY [AgpdbQuestionID]
                FOR XML PATH('')
                ), 1, 1, '') AS NVARCHAR(max))

Answer 1

This is because of QUOTENAME, if input is larger than 128 it returns NULL because it is supposed to handle sysname, not (N)VARCHAR: "character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL."

Instead try:

SELECT QuestionList = cast(STUFF((
                SELECT ',' + '[' + (cast(question AS NVARCHAR(max)) + ']')
                FROM (
                        VALUES (REPLICATE('a', 130)) 
                     )q(question)
                FOR XML PATH('')
                ), 1, 1, '') AS NVARCHAR(max))