XML variable returning empty string after hitting specific size limit about 44KB

Question

I am hitting what appears to be a size limit for XML data type in the 43KB range which is much less than the 2 GB advertised.    Changing the data type to NVARCHAR(MAX) exhibits the same "size limit" behavior. 

Behavior of the code snippet below is that up to a 140 rows, the variable returns XML as expected.  At 141 rows (for this particular example),  it returns an empty string.  NOTE:  the query returns ALL rows regardless of size outside the variable.

Failure is occuring both on multiple local copies of SSMS as well as when running as a SQL Server Agent job. Environment is SQL Server 2005 9.00.4053.00 (X64)

Found an identical scenario here, but there was no resolution.
http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/8288f148-15b5-4317-8518-92217fc16a17

Is there a configuration setting or is there some other cause?

------------example--------------
--------data prep--------
DECLARE @t TABLE (ClientId INT, FirstName VARCHAR(50), LastName VARCHAR(50), BatchId SMALLINT, SentDate Datetime, Amount MONEY, response_date DATETIME, response_code CHAR(3), response_text VARCHAR(50), response_type CHAR(1),debit_credit CHAR(1), [Description] VARCHAR(50), DraftNumber TINYINT, ClientStatus VARCHAR(50))
 
DECLARE @ix SMALLINT
SET @ix = 1
 
WHILE (@ix <= 141)
BEGIN
 INSERT INTO @t VALUES(1234567, 'Joe', 'Bob', 999, '2009-10-15 00:00:00', 29.99, '2009-10-20 00:00:00', 'R01', 'Insufficient Funds', 'R', 'D', 'invoice',1, 'Not Current')
 SET @ix = @ix + 1
END
 
--------------create xml-------------
DECLARE @xml XML
SET @xml = (SELECT * FROM @t FOR XML RAW ('ACH'))
 
SELECT @xml -- returns empty at 141 rows, returns xml for less than 141 rows

SELECT *

 FROM @t FOR XML RAW ('ACH') -- returns ALL rows at 141 and above

Answer 1

In Tools, Options, Query Results, SQL Server, Results to Grid, there is an option for XML data length.  Try increasing that and report back.

Answer 2

Thank you for the reply.

Maximum characters retrieved   XML Data:  2MB

I added to the example to illustrate the difference between XML variable and XML from a query.   Also, this behavior is also exhibited when run as a job at the server level.

Answer 3

Well your code works fine for me even if I set the limit to 9999.  Maybe it's some kind of Service Pack thing?  What edition and service pack of SQL Server are you running?

Answer 4

To clarify, up to 140 rows, SELECT @xml and SELECT * FROM @t FOR XML RAW ('ACH') return identical results.  At 141 rows and above, SELECT @xml returns an empty string while  SELECT * FROM @t FOR XML RAW ('ACH') continues to return the expected XML. 

Answer 5

>>What edition and service pack of SQL Server are you running?

@@version = Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01.  

>>Well your code works fine for me

Yeah, the code works on my home system, too.  My favorite kind of problem:  not easily reproduced.

>>some kind of Service Pack thing?

I believe that is the correct direction to go. 

Is your system 32 BIT or 64 BIT? 

My workstation is 64 BIT Vista and the servers are 64.  I have run this code on other desktops -- ALL are 64 Bit VISTA.  But, I just located a workstation (completely by accident) where this code is running correctly.  So far,  the only difference I see is that the 'good' machine is 32 Bit XP.

Answer 6

You might consider logging a connect:

https://connect.microsoft.com/SQLServer


I can't reproduce the problems on my 32 bit laptop running Windows XP, or a 64-bit VM running Windows Server 2003.  Good luck!

Let us know how you get on.

Answer 7

Thanks for the feedback.  I have started testing multiple variables -- OS, SSMS vs Stored Procedture, etc.  So far, getting surprisingly variable results.  I am pushing this up to Microsoft after I complete further testing. Will post results.  Again, thank you for the feedback.

Answer 8

Results are in ...   Example query is same as above.  Stored procedure uses example query to insert to a table column.

Device	Operating System	Software	Test	Expected Result	Actual Result
Workstation	Vista 64 Bit,	SQL Server 2005 9.00.4053.00 (X64)	SSMS example query	2 identical XML results	A) an empty string and B) XML
Workstation	XP 32 Bit	SQL Server 2005 9.00.4053.00 (X64)	SSMS example query	2 identical XML results	2 identical XML results
Workstation	XP 64 Bit	SQL Server 2005 9.00.4053.00 (X64)	SSMS example query	2 identical XML results	2 identical XML results
Remote Desktop Connection	Windows Server 2003	SQL Server 2005 9.00.4053.00 (X64)	SSMS example query	2 identical XML results	2 identical XML results
Server/SQL Server Agent	Windows Server 2003	SQL Server 2005 9.00.4053.00 (X64)	Stored Procedure	XML inserted to table	empty string inserted to table

Answer 9

Summary of the results above.

Results vary based on operating system and method.

IF OS = XP 32 bit OR XP 64 bit OR Windows Server 2003 64 bit
    AND method = SSMS
THEN example query works as expected.

IF OS = Vista 64 bit AND Method = SSMS
THEN example query fails -- i.e XML variable returns empty string at 141 rows

IF OS = Windows Server 2003
   AND method = Stored Procedure run from SQL Server Agent
THEN XML variable returns empty string at 141 rows

Stored procedure was tested by inserting XML data  to a table.

Answer 10

Sorry for the delay in reporting back.  The problem is SQL Server 2005 with Vista SSMS. 

There is no data loss with XML data type.  There is only the appearance of data loss because Vista SSMS 2005 does not correctly display XML datatype columns or variables when size exceeds 43KB.    Appears that NVARCHAR(MAX) may have the same limitation, but I have not conclusively reproduced that.

Final test that caused me to reach this conclusion:

1.  CREATE TABLE dbo.xmltest (x XML)
2.  INSERT INTO dbo.xmltest VALUES (@xml) ... where @xml is XML over 43KB
3.  SELECT * FROM dbo.xmltest
      a.  From Vista SSMS      empty string is displayed
      b.  From XP SSMS      XML is correctly displayed

Microsoft was able to reproduce the error and has entered it as a bug fix.    Microsoft reports that the bug does NOT occur in SQL Server 2008. 

My workaround was to revert back to XP.  Other workarounds include to not display results in grid and to upgrade to 2008.

Answer 11

Hello,

Does anyone know if this bug fixed for SQL 2005 (Developer edition)?

I'm running Microsoft SQL Server 2005 - 9.00.5000.00 (X64)   Dec 10 2010 10:38:40   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1).

And the problem you described is exactly the same thing is happening here...My computer has WindowsXP32bit and I can see/send the xml without any problems....the problem resides in computers that run Windows7 and ON Windows Server 2008...

 

Thanks

---

JaimePR,