sql server xp_cmdshell echo NVARCHAR(MAX) string

I'm trying to use master.sys.xp_cmdshell to echo a extremely large string. I believe the max of varchar which I think is something around 8000 characters is too small so I'm not sure how to accomplish this because xp_cmdshell expects the string to be varchar.

My @MESSAGE variable is declared as NVARCHAR(MAX) and I need every bit of it. Here is my code:

DECLARE @EXECUTION_STRING NVARCHAR(MAX) = 'ECHO ' + @MESSAGE + ' > "' + @FULL_PATH + '\' + @FILE_NAME + '"'
EXEC master.sys.xp_cmdshell @EXECUTION_STRING

So, my question is; how can I accomplish a string to file write when the string is of type NVARCHAR(MAX)?

New plan, based on @Arun's suggestion of creating an SP with the BCP functionality. Here is my code so far:

DECLARE @THCIC_OUTPUT_TABLE             VARCHAR(255) = '##temp' + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
DECLARE @RESULT                         INTEGER
DECLARE @THCIC_OUTPUT_EXECUTION_COMMAND NVARCHAR(MAX) = ' 
                                                        CREATE TABLE [' + @THCIC_OUTPUT_TABLE + '] ( CONTENT NVARCHAR(MAX) )

                                                        INSERT INTO [' + @THCIC_OUTPUT_TABLE + ']
                                                            SELECT @MESSAGE
                                                      '

EXECUTE SP_EXECUTESQL @THCIC_OUTPUT_EXECUTION_COMMAND, N'@MESSAGE NVARCHAR(MAX)', @MESSAGE

DECLARE @THCIC_OUTPUT_FILE_WRITE_COMMAND VARCHAR(1000) = '
                                                          BCP ”SELECT CONTENT FROM [' + @THCIC_OUTPUT_TABLE + ']"
                                                          QUERYOUT "' + @FULL_PATH + '\' + @FILE_NAME + '" -w -T -S ' + @@servername 

PRINT @THCIC_OUTPUT_FILE_WRITE_COMMAND

EXECUTE @RESULT = master.sys.xp_cmdshell @THCIC_OUTPUT_FILE_WRITE_COMMAND, NO_OUTPUT
PRINT @RESULT
EXECUTE ( 'DROP TABLE [' + @THCIC_OUTPUT_TABLE + ']' )

So, the frustrating part is that I'm receiving an error, yet my file is not being written. The output of PRINT @THCIC_OUTPUT_FILE_WRITE_COMMAND is:

BCP ”SELECT CONTENT FROM [##temp878274]"
QUERYOUT "\\TXPDC-FS01\Profiles\cofarmer\My Sandbox\THCIC\Q2_2014\Burleson\PIPSUB2938718184092014251607.txt" -w -T -S TXPDC-STKSQL01

While the output of PRINT @RESULT is: 1

What am I doing wrong?

Solution

You can't on xp_cmdshell, because windows xp command line string limit is a little over 8,000 chars. Your better off making another stored procedure for bcp.