Is there are a size limit on XML datatype SQL Server 2008 R2 ?

Question

Is there are a size limit on XML datatype SQL Server 2008 R2 ?

nikshepmehra

Mr or Mrs. 500

Points: 587
More actions
February 4, 2011 at 10:07 pm

#233182

Is there are a size limit on XML datatype SQL Server 2008 R2 ?
I am facing an issue on my current system where the XML is not getting saved on the database if it is exceeding 10,000 chars.
Initially when we had designed the system we had assumed that XML would allow us a higher capacity of data in a single row column as compared to varchar(max).

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The XML data type is capped at 2GB. Unfortunately that means you must be encountering some other error if it is truncating before that.

How are you going about checking this?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

nikshepmehra Mr or Mrs. 500 Points: 587 More actions · Answer 2

Thanks for the confirmation I did read that 2gb is allowed. We found out that our WCF service is failing it is configured for a high volume of data but somehow we are unable to transfer it across our WCF service.

Thanks again.

Tom257 SSChampion Points: 11543 More actions · Answer 3

I'm sure there is something like maxMessageSize in the WCF config files - you can change it up to 2GB, otherwise it truncates around 8192.

Check system.ServiceModel in the config files.

bindings/httpBindings

Somewhere there are maxMessageLength or MaxMessageSize settings, possibly maxBufferSize rings a bell.

I set one up recently, but don't have access to the source code until tomorrow morning, I can find the exact settings then if you're still having problems.

EDIT: Must be something wrong with the Site, this came up in the most recent postings. - and its 10 months old!

keapdx SSC Enthusiast Points: 102 More actions · Answer 4

I've got a variation on this problem. I'm getting data via OPENQUERY from an xml field on the remote (Sybase) database to an xml field on the local SQLServer DB.

The remote field is over 33K. The insert from the OPENQUERY result fails.

If I get the xml field as varchar(max) instead and try to convert from varchar to xml the convert fails too.

So -- how do I get a particularly long piece of xml from one place to the other?