January 3, 2002 at 10:12 am
I want to store the ouput of
select * from <table> for xml auto,elements
into a variable.
Actually i want to return that variable as a ouput parameter.
January 3, 2002 at 10:32 am
You will have problems because the limit for a parameter is varchar( 8000) and an XML doc can easily exceed this.
Form BOL, Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client.
What do you want to do?
Steve Jones
January 3, 2002 at 10:58 am
Steve,
Thx for the response.
But the SP can take input as text and can return ouput as text.
So if this is possible, then iam trying to figure out how can store the ouput of the resultset of for xml and return it as an ouput parameter to the middle tier persons.
Thx,
Krishnan
January 3, 2002 at 11:18 am
The for xml is built to return a stream, not really a result set.
You could probably built something, but can the middle tier not handle a stream? They can also reformat the normal recordset as XML using ADO.
Steve Jones
January 3, 2002 at 2:54 pm
One problem with returning the xml as a result set is that it is truncated at 2033 characters I believe. (not sure if that is the exact number) The result set shown in query analyzer might show the entire xml string but when you try and access it, it is truncated. To avoid this, I believe you can use the stream object.
January 3, 2002 at 4:53 pm
There is a trace flag that will return "pretty" output - but still if you're using XML, use the stream.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply