5

I am trying to concatenate results of some string data in a query using XML PATH

See query below.What happens is that the XML concatenated column statement gets truncated.Seems like there is a limitation on maximum length.How do I overcome this.

select SUBSTRING(statement,1,len(statement)-2)+';'
from(
select 
'update '+tab.table_name +' set ' +
(
select 
col.COLUMN_NAME +'=replace('+col.column_name+',''@xx'',''yy'') ,'+CHAR(10) 
from INFORMATION_SCHEMA.COLUMNS as col
where  tab.TABLE_CATALOG=col.TABLE_CATALOG
and tab.TABLE_SCHEMA=col.TABLE_SCHEMA
and tab.TABLE_NAME=col.TABLE_NAME
and col.DATA_TYPE in('VARCHAR','NVARCHAR')
for xml path('') )  as statement
from information_schema.TABLES as tab
) as x
where statement is not null
josephj1989
  • 9,509
  • 9
  • 48
  • 70
  • Is it concatenated when you pull data back from an application, or in SSMS? – Cᴏʀʏ Jun 24 '10 at 19:49
  • How are you executing the above? If it's in SSMS then there is a limit to the number of characters that it will display. That can be changed with an option. – Tom H Jun 24 '10 at 19:52
  • 1
    I apparently can't edit my comments here; I meant to say __truncated__, not concatenated. – Cᴏʀʏ Jun 24 '10 at 19:55
  • It is in SSMS- following cory's suggestion I have changed the limit in SSMS and it works fine. Thanks everyone. – josephj1989 Jun 24 '10 at 20:21

1 Answers1

14

Assuming you're seeing the truncation in SSMS, change your maximum character settings in SSMS's options:

  • Tools > Options > Query Results > SQL Server > Results to Text > Maximum number of characters displayed in each column

    (limit is 8192 characters)

  • Tools > Options > Query Results > SQL Server > Results to Grid > Maximum Characters Retrieved 

    Non XML data limit is 65535 characters
XML data limit is Unlimited
DonBecker
  • 2,424
  • 3
  • 25
  • 43
Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194