XML size in a stored procedure

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

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?

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

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.

yes

then I don't know how this could be "truncated" ... except maybe in the code

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

Thanks