CODEWITHSUNDEEP
xmlt-sqlconcatenation
H.Ben
H.Ben

Reputation: 117

Stuff and 'For Xml Path' in Sql Server

I am using the following code to concatenate all my result into one record (separated by carriage returns)

SELECT  [ConcactColumn] =  STUFF((
      SELECT CHAR(10) + t.column 
      FROM #table t
      FOR XML PATH('')), 1, 1, ''
      )

The only issue is I am not able to have a result with more than 43,720 characters when I need to fit at least 5,000,000 .

how can I achieve this ? thank you in advance.

Upvotes: 2

Views: 10592

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

The size of a string in SSMS is limited. But the Size of an XML is not

  • right click into query window
  • options
  • Results in grid
  • Set XML to "umlimited"

Then try this:

SELECT STUFF(
    (
      SELECT CHAR(13) + CHAR(10) + t.name
      FROM sys.objects t
      FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'), 1, 2, '')
FOR XML PATH('');

I cut away 2 characters with STUFF() due to CHAR(13)+CHAR(10). With CHAR(10) only you must change this to ...),1,1,'').

If the first line might stay blank you can go without STUFF

SELECT
    (
      SELECT CHAR(10) + t.name
      FROM sys.objects t
      FOR XML PATH(''),TYPE
    ).value('text()[1]','nvarchar(max)')
FOR XML PATH('');

Click the XML and you see a result which is limited by your machines capacity only...

Upvotes: 2

Alan Burstein
Alan Burstein

Reputation: 7918

This used to give me fits. I love this stored procedure and, although I wish I wrote it, I didn't (credit in the comments). 

CREATE PROCEDURE dbo.LongPrint @string nvarchar(MAX)
AS
/* 
Source:
https://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html

Example:
exec LongPrint @string = 'This String Exists to test the system.'

This procedure is designed to overcome the limitation in the SQL print command that causes 
it to truncate strings longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000 characters. If there 
are carriage returns (CRs) or new lines (NLs in the text), it will break up the substrings
at the carriage returns and the printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will print it out in blocks of 4000 
characters with an extra carriage return at that point.
If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used when actually needed. */

DECLARE 
  @CurrentEnd bigint, /* track the length of the next substring */
  @offset tinyint     /*tracks the amount of offset needed */

set @string = replace(replace(@string, char(13)+char(10), char(10)), char(13), char(10));

WHILE LEN(@String) > 1 BEGIN
  IF CHARINDEX(char(10), @string) BETWEEN 1 AND 4000
  BEGIN 
    SET @CurrentEnd = CHARINDEX(char(10), @String) -1;
    SET @offset     = 2;
  END
  ELSE
  BEGIN
    SET @CurrentEnd = 4000;
    SET @offset     = 1;
  END;

  PRINT SUBSTRING(@String, 1, @CurrentEnd);

  SET @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822);

END /*End While loop*/

You would use the proc like this:

DECLARE @results varchar(max);

SELECT @results =  STUFF((
      SELECT CHAR(10) + t.column 
      FROM #table t
      FOR XML PATH('')), 1, 1, '');

EXEC dbo.LongPrint @results;

So we're clear: This solution is not designed for blazing fastness.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

If you are trying to copy from the results grid, take a peek here http://connect.microsoft.com/SQLServer/feedbackdetail/view/951995/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode

Perhaps this can help

Declare @S varchar(max) = ''
Select @S = @S + char(10) +  t.column 
 From  #table t
 Where t.column  is not null

Select Len(@S)

SELECT  [ConcactColumn] = @S

Upvotes: 2

Related Questions