for xml path()有长度限制吗？

xiangaylian 2012-03-08 10:36:56
测试数据：


if object_id('tempdb..#t') is not null

	drop table #t

create table #t(a nvarchar(50))

declare @a int

set @a = 1

while @a <= 120

begin

	insert into #t(a) values('Employees.Number as [工號]★')

	set @a = @a + 1

end

--print len('Employees.Number as [工號]★') * 120

select '' + a + '' from #t for xml path('')

正常的长度是3000，但for xml path('')出来的数据长度无论怎么样都只有2033

请问是什么原因造成的，有什么办法解除这种限制，使其正常输出呢？
...全文
546 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
justbit 2012-03-08
  • 打赏
  • 举报
 回复
http://social.microsoft.com/Forums/en-IE/sqlserverzhchs/thread/56a15780-4b78-47c4-8aa9-034c335cd265
xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 ap0405140 的回复:]
My sql server version is SQL2008R2 and I didn't change the database default setting with XML, What about yours?

Please run the script for test by four floor.
[/Quote]
我的运行结果和4楼一样，只是显示结果不对，被截断了。
xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 acherat 的回复:]
工具--选项--查询结果--以网格显示结果

看看XML数据显示。
[/Quote]
这里刚刚对工具--选项--查询结果--以网格显示结果--检索最多的字符数--XML数据
由2M-->5M和无限制
在新查询窗口中完全没有效果呢！
勿勿 2012-03-08
  • 打赏
  • 举报
 回复
看看是不是环境问题。还是显示的问题
AcHerat 2012-03-08
  • 打赏
  • 举报
 回复
工具--选项--查询结果--以网格显示结果

看看XML数据显示。
xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复
2005显示的长度有限制么？能否修改的！
xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 fredrickhu 的回复:]
还有 你的数据库版本是？
[/Quote]
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)
AcHerat 2012-03-08
  • 打赏
  • 举报
 回复
网格形式显示的长度是有限制的。
唐诗三百首 2012-03-08
  • 打赏
  • 举报
 回复
My sql server version is SQL2008R2 and I didn't change the database default setting with XML, What about yours?

Please run the script for test by four floor.


--小F-- 2012-03-08
  • 打赏
  • 举报
 回复
还有 你的数据库版本是？
--小F-- 2012-03-08
  • 打赏
  • 举报
 回复 
if object_id('tempdb..#t') is not null

    drop table #t

create table #t(a nvarchar(50))

declare @a int

set @a = 1

while @a <= 120

begin

    insert into #t(a) values('Employees.Number as [工號]★')

    set @a = @a + 1

end

-- print len('Employees.Number as [工號]★') * 120

select '' + a + '' from #t for xml path('')





select len('Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employee

s.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★Employees.Number as [工號]★'

)

/*-----------

3002



(1 行受影响)*/
开着拖拉机泡妞 2012-03-08
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 fredrickhu 的回复:]

你数据库的查询选项--结果--网格 里面检索最多的字符数XML数据设置大一点
[/Quote]
--小F-- 2012-03-08
  • 打赏
  • 举报
 回复
你数据库的查询选项--结果--网格 里面检索最多的字符数XML数据设置大一点

xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复
各位能否把查询的结果拷出来看看
xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复 


select len((select '' + a + '' from #t for xml path('')))

--这个结果是3000，我这也是的

select '' + a + '' from #t for xml path('')

--这个查询出的结果被截断了

--最后显示的是“Employees.Number as [工號]★Employee”，总长度只有2033
--小F-- 2012-03-08
  • 打赏
  • 举报
 回复
如果正常的长度是8000呢？？出来的长度是多少？
xiangaylian 2012-03-08
  • 打赏
  • 举报
 回复
难道与我的数据库设置有关系？
唐诗三百首 2012-03-08
  • 打赏
  • 举报
 回复
测试结果都是3000呀,


if object_id('tempdb..#t') is not null

    drop table #t

   

create table #t(a nvarchar(50))



declare @a int

set @a = 1

while @a <= 120

begin

    insert into #t(a) values('Employees.Number as [工號]★')

    set @a = @a + 1

end





select len('Employees.Number as [工號]★') * 120 'len1'

select len((select '' + a + '' from #t for xml path(''))) 'len2'





len1

-----------

3000





len2

--------------------

3000
AcHerat 2012-03-08
  • 打赏
  • 举报
 回复 


select len((select '' + a + '' from #t for xml path('')))



/****************



3000
开着拖拉机泡妞 2012-03-08
  • 打赏
  • 举报
 回复
百度看看吧，这个不知道
加载更多回复（1）
for xml path 字符长度问题
.Net 获取查询的数据的时候 用for xml path 拼接字符串  发现读取出来的数据如果超过2033 就自动截断，后来发现网上有用创建临时表的方式插入数据，比较麻烦，有个简单的方法可以直接读取出来，也不用那么麻烦 select ''''+ t.OrderID+''','      from RefundBathNo t      where OrderID != ''     
sqlserver多行转一行字符串（解决for xml path 超过2033截断问题）
1、t-sql：经过测试此方法在数据量较大时候效率特别低。2、for xml path：此方法效率可以但是需要注意利用stuff函数将生成的xml文件转换为字符串，否则用sqldatereader或者SqlDataAdapter读取数据只能读取2033个字符。例如：有如下test表有数据Id Name1 a2 b3 c4 d当使用：SELECT','+IdFROMdbo.Test...
FOR XML PATH及行列变换，SUBSTRING_INDEX行转换
FOR XML PATH基础及行列变化；行转列；
【转】MS SQLSERVER SELECT FOR XML 中字符的限制问题

采用sqlcommand返回单行的值这种方式查询的xml长度不能超过8000个字符。
 
ssql = "select * from " + tablename + " FOR XML AUTO,ELEMENTS";     
                SqlCommand command = new SqlCommand(ssql, connection);
                resultXml = (string)SqlHelpe
for xml path(’’) 和 STUFF的用法，写的很详细。转载
https://www.cnblogs.com/mu-zi/p/5627902.html 今天在项目中遇到一个问题，在综合查询的时候，需要把同一个表中，同一个人的某个属性加起来放在一个字段中，这时候需要for xml path(’’) select * from t_Baseinfo bi left join ( 　　select DISTINCT b.PersonID, 　　 STU...
应用实例

27,582

社区成员

68,548

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章