I'm trying to apply techniques I'd normally use to pull data from SQL/Server
to pull XML from SQL/Server, and I'm running into a problem.  Here's what
I'm doing:

1.  I've built a stored procedure that pulls my data, and renders it into
proper XML.  I'm using 'FOR XML AUTO, ELEMENTS', but I'll bet the issues
would occur with EXPLICIT also.
2.  Using VS2005, I've added a DataSet item, and have dragged the sProc into
the dataset so I can access it through a TableAdapter.
3.  When I fill the DataTable via the TableAdapter, I get a single column
limited to 2033 characters, so I'm not getting the entire XML tree.

Google has a number of posts regarding using OLEDB / Streaming to get the
data from the 2033 character fields, but none of the posts address the use
of strongly typed datasets and TableAdapters, which are what I'm using.

Can this be done?  If so, how?

Thanks.

Hi Ben,
I understand that you found that when you used TableAdapter to retrieve an
XML format result from your procedure, the data field only contained 2033
characters.
If I have misunderstood, please let me know.

From my research, this is a known issue that this behavior occurs because,
for XML results greater than 2,033 characters in length, SQL Server returns
the XML in multiple rows of 2,033 characters each.

If you use TableAdapter to retrieve the data, you may need to concatenate
the fields values from the returned rows. For example:
               for (int i = 0; i < table.Rows.Count;++i )
                    strQueryResult += table.Rows[i][0].ToString();

Otherwise, you may use ExecuteXmlReader to retrieve the data from those
queries for XML.

You may also refer to this KB article:
The XML data row is truncated at 2,033 characters when you use the
SqlDataReader object
http://support.microsoft.com/kb/310378/en-us

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Hi Ben,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Charles,
    Thanks for your earlier suggestion.  I adapted it by using a 'foreach',
returning rows of my specific type, and a StringBuilder to develop the full
XML string.  Same concept, a little different twist.  With that I was able
to deal with the full XML document.
    My question remains: since (as I understand it) you'd never use the 2033
character chunks of the XML document, and you'd always want the full
document, why doesn't the TableAdapter return that?  I know it's only a few
lines of code for me to build the full document from the pieces, but I don't
understand why that isn't built into the class automatically.
    Thoughts?
        Ben

Show quote

Hide quote

Hi Ben,
Thanks for your response at the newsgroup.

For your current concern, I agree that it is a good idea if current
TableAdapter or SqlDataAdapter can encapsulate this function automatically.
I recommend that you give Microsoft feedback via
https://connect.microsoft.com/Visualstudio so that your suggestion can be
routed to the product team and hope that this feature will be included in
the next release of .NET library. You should be able to get email
notifications from the product team once they respond to your feedback.

By the way, I noticed that you used a different email alias to reply me at
the newsgroup. Kindly I recommend that you could reply at the newsgoup with
your registered no-spam posting alias in future since currently our
internal tool only monitors the replies from those registered no-spam
aliases. If a customer uses a different email alias to post back, we cannot
get any notification for his reply and in this case, we may not be able to
assist him further. We will submit a feedback to our internal tools Dev
team, however the process may need a long time, hence currently I recommend
that you use your registered no-spam alias for your posts and replies in
future. Appreciate your understanding on this. Today luckily I found your
reply (did not exceed 2 business days) when I performed research for
another post; otherwise I may not be able to reply you.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

How to output table as XML with elements on separate lines and indented?
How to define the XML schema for a SQLXMLBulkLoad
Loading records from XML document
Get data out of a table using a schema through select statement
Need help with XML Bulk Load