XML Length Limit

Question

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

See request below. What happens is that the expression of the XML concatenated column is truncated. There seems to be a limit on the maximum length. How can 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

Answer 1

Assuming you see truncation in SSMS, change the maximum character settings in SSMS settings:

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

  (limit is 8192 characters)
  

• > > a > SQL Server > > ,

  Non XML data limit is 65535 characters
  XML data limit is Unlimited