We help IT Professionals succeed at work.

XML Size Limit in SQL Server 2005

fesnyng
fesnyng asked
on
919 Views
Last Modified: 2012-08-13
I am hitting some sort of size limit for XML data type in the 43KB range which is much less than the 2 GB advertised.  

Code snippet below illustrates the failure on my machine.  As noted below, this may not be reproducible on your machine.

In the code snippet, up to a certain number of rows, the variable returns XML as expected.  At 141 rows (for this particular example),  it returns an empty string.

Failure is occuring both on my local copy of SSMS as well as when running as a SQL Server Agent job.

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

But, if you read the link, you will find that the situation was not reproducible AND the original problem simply went away when the poster changed machines.  

It is not going away for me.   Is this a setting of some sort?  Is there a problem with using a variable of XML data type?


--------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

Open in new window

Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Welll... I cannot reproduce this behavior at my home system either.  That is the most worrisome part.

My apologies, but I missed including an important part of the illustration -- SELECT * FROM @t FOR XML RAW ('ACH') is the subquery that is populating the XML variable.  IF that SELECT is run along with the SELECT @xml the results are:

1.  Up to 141 rows -- both SELECTs return an identical result
2.  141 rows and greater, SELECT @xml returns an empty string, but SELECT * FROM @t FOR XML RAW ('ACH') returns the expected XML.

I did set my Grid Results 'Maximum Characters Retrieved ... XML Data:  to Unlimited.  No change in behavior.








--------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 XML data in all cases

Open in new window

Author

Commented:
Tested a number of different devices.  Found that the results varied based on operating system and method.

IF OS = XP 32 bit, XP 64 bit or Windows Server 2003 64 bit
    AND method = SSMS
THEN example query works as expeted.

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.






Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Really ? That is amazing, wonder why we haven't heard that before... That is a great piece of detective work by the way...

Question for you, you said it was 64bit Vista , but which version/edition of SQL Server 2005 ?  Standard / Enterprise / Express / 32bit / 64bit ?

Author

Commented:
>>wonder why we haven't heard that before..

There are a couple of instances reported, but these had no real resolution.  (links below).  My guess is that there are not many database developers using Vista, not many using SQL Server 2005 XML and even fewer storing XML over 44KB.  Multiply the probabilities to get a very small final probability of having this problem..  At least, that is my guess.

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/8288f148-15b5-4317-8518-92217fc16a17
http://stackoverflow.com/questions/934430/size-limit-for-xml-datatype-in-sql-2005

>>version/edition of SQL Server 2005
Enterprise edition.  @@version = 9.00.4035.00 (X64)
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Thanks fesnyng

You are probably quite correct, throw in SQL 2005 Enterprise X64 on Vista and that would narrow it down even further I think...

Cheers,

Mark Wills

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.