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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With