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))

Statics

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))

Sg

Ahhh - Thanks, I was looking into the stuff and XML if it had any limitations. Didnt think about the QOUTENAME :) – Statics

I had a similar issue recently myself, it did take a bit of digging to find the cause. – Sg

I recommend '[' + REPLACE((cast(question AS NVARCHAR(max)),']',']]') + ']' if you want to properly replicate QUOTENAME's functionality. – Synchrocyclotron

Parentheses correction on comment from Thom A: PRINT '[' + REPLACE(cast('some string' AS NVARCHAR(max)),']',']]') + ']' – Secessionist

Answer 2

Just as another way of achieving this. This method achieves the same without using XML so that you aren't restricted to certain characters. It itterates through your table, building the string with each row, with the last instance being set to your variable @QuestionList.

Declare @QuestionList AS NVARCHAR(max)

SELECT 
    @QuestionList = isnull(@QuestionList + ', ', '') + question
FROM
    questions
ORDER BY 
    AgpdbQuestionID

It is important to use the isnull, as this achives ommiting the first comma when the existing string is null.

I'd be intregeagued to see how efficient this is compared to the XML method, but this has been useful for myself when I've needed ceratin characters like >, <, " and '

Lipo