XML size in a stored procedure

Posted on 2015-02-03
Last Modified: 2015-02-11
I have a stored procedure on SQL Server 2008 that returns an xml document.

It appears that the size of the xml is being truncated to 2Mb.

I have modified the max size of xml in Management Studio to unlimited but I still get the problem.

I wonder if there is some limitation on the size of the variable declared in the stored procedure, or the size of the result set?

Thanks for any advice.
Question by:soozh
  • 4
  • 4
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40586104
>It appears that the size of the xml is being truncated to 2Mb.
this is only the default output indeed.
if you check the len( youxml ), you will get the proper value, normally.

Author Comment

ID: 40586111
len(yourxml) does not work because it does not like xml data types.  Datalength does.

I have changed the outputr size to unlimited.

Somewhere i am being truncated to 2mb.

An should this SP be run by SQL Server Agent how do it set the size to unlimited there?
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40586129
>Datalength does.
yes, sorry. does it show the good size value?

>An should this SP be run by SQL Server Agent how do it set the size to unlimited there?
the question is what are you doing with the "output", in regards to sql server agent?

Author Comment

ID: 40586171
With regards to the size issue when i run the sp in management studio:

The sp returns the xml, but when i click on it i get a message that its is malformed.  When i copy it and paste it into a file i get a file that is about (plus mins a few byte) 2mB

So i think that either the xml is truncated in the sp to 2Mb, or the transfer to Management Studio truncates the resulting xml to 2Mb.

I have changed the size to unlimited.

With regards to SQL Server Agent:

Just a question.  I have no intention of doing it but i was just wondering about other environments suchs an application.
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 40586236
so, you confirm to have set in the Query Options, Grid, XML data to "unlimited" ?

Author Comment

ID: 40586379
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40588428
then I don't know how this could be "truncated" ... except maybe in the code

Author Comment

ID: 40602810
The problem seems to be solved when i rebooted everything.  The solution seems to be GRID XML data unlimited.


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query for filter 12 34
BULK INSERT most recent CSV 19 45
Convert SP in a format for debugging 7 30
Generate Scripts of Schema/Data with "WHERE" clause 6 20
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now