对于 XML 长度限制 [英] For XML length limitation

问题描述

我正在尝试使用 XML PATH 连接查询中某些字符串数据的结果

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

请参阅下面的查询.发生的情况是 XML 连接的列语句被截断.似乎对最大长度有限制.我该如何克服这个问题.

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

推荐答案

假设您在 SSMS 中看到了截断，请在 SSMS 的选项中更改最大字符数设置:

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

• 工具 > 选项 > 查询结果 > SQL Server > 结果转文本 > 每列显示的最大字符数

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

(limit is 8192 characters)

工具 > 选项 > 查询结果 > SQL Server > 结果到网格 > 检索的最大字符数

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

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

这篇关于对于 XML 长度限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！