Generate XML document problem

I'm having the same problem as described below. Although this was
posted on usenet a few years ago, a solution was not posted, and I
can't find one on the internet anywhere. Does anyone have any idea as
to how I might workaround this? Note that I am not sorting on any of
the varchar fields that are causing the problem.
Thanks,
Aaron

> Here is the problem,
> I need to generate an XML document using FOR XML Explicit. My select
> works fine but I run into a problem when I try to do an order by with
> a row that is greater than 8094 bytes. SQL Server outputs the error
> "Cannot sort a row of size 96555, which is greater than the allowable
> maximum of 8094".
> The order by is very important in my query, because without it. I will
> not get the XML document in the correct schema.
> I have researched this issue and have come to the conclusion there is
> not a way I know of to get around this limitation. I tried using the
> query hint "Robust Plan" and "Force Order" and that did not work.
> A solution that I thought of, but don't know if it will work and how
> to do in SQL server is to first generate the XML schema without the
> TEXT column fields and then do another query to load the text columns
> into the XML document.
> Can I and how do I do all of this in a Stored Procedure?
> Or is there a better workaround for my problem?
> Thanks in advance
>
> --
> B. Harilal
Hi Aaron
Can you please provide some more information? Is it the order by clause that
may contain too much data? Or the rows that you sort?
It would be very useful if you can provide a simple repro.
Thanks
Michael
"AaronS" <gcsdba1@.yahoo.com> wrote in message
news:1140618933.285133.96480@.z14g2000cwz.googlegro ups.com...
> I'm having the same problem as described below. Although this was
> posted on usenet a few years ago, a solution was not posted, and I
> can't find one on the internet anywhere. Does anyone have any idea as
> to how I might workaround this? Note that I am not sorting on any of
> the varchar fields that are causing the problem.
> Thanks,
> Aaron
>
|||Here's an example of what the query is trying to do. I know this query
is not logically correct, but hopefully it gives you an idea of what
the actual query is doing.
SELECT
1 as Tag,
NULL as Parent,
reportID as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM report
UNION ALL
SELECT
15 as Tag,
5 as Parent,
NULL as [reportData!1!id],
reportID as [comments!5!reportCommentID!hide],
details as [comment!15!details] -- varchar(8000)
NULL as [Notes!10!reportCaseID!hide],
NULL as [Note!30!noteDesc]
FROM comment
UNION ALL
SELECT
30 as Tag,
10 as Parent,
NULL as [reportData!1!id],
NULL as [comments!5!reportCommentID!hide],
NULL as [comment!15!details],
reportID as [Notes!10!reportCaseID!hide],
NoteDesc as [Note!30!noteDesc] -- varchar(8000)
FROM note
-- Note that the sort is not on any of the "long" varchar fields
ORDER BY
[reportData!1!id],
[comments!5!reportCommentID!hide]
[Notes!10!reportCaseID!hide]
|||Hi Michael,
I realize my SQL example is very crude (especially since I left out any
join conditions), but does it get the idea across about where the
problem lies? It appears to be the rows I am attempting sort that
contain too much data for SQL Server to handle. The columns specified
in the order by clause do not appear to be the culprit as this error
occurs only when the selected data itself exceeds the SQL Server limit
of 8094 characters. Can you (or anyone else reading this) provide any
insight? I would really appreciate it. I'm at a loss of where to go
from here except to start truncating the lengthy varchar strings. I
don't even know if I have the option to do that.
Aaron