Reading a large XML string from a TSQL FOR XML statement in C # .NET

Question

Have a SQL command with FOR XML that returns one long XML as SqlString. My problem is reading a long string of XML in .NET C #.

The following only read the first 2033 characters

SqlDataReader rdr = command.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); Debug.WriteLine(rdr[0].ToString().Length.ToString()); } 

I also tried the command .ExecuteScalar and rdr.GetString and still get the first 2033 characters. I changed the sort, and it still truncates in 2033, so it’s hardly caused by a bad character. Tried rdr.GetSqlXml and receive error message cannot pass SqlString to SqlCachedBuffer.

If I restrict SQL to returning less than 2033 characters, I get the full valid XML. Therefore, I do not think this is an XML parsing problem, not just truncation. I don't need XML parsing - it's really XML from the TSQL statement, which I need as a string.

How can I read the full XML (like text)?

TSQL works.

  select top 10 docSVsys.sID, docSVsys.docID , (select top 10 value + '; ' from docMVtext with (nolock) where docMVtext.sID = docSVsys.sID and docMVtext.fieldID = '113' order by value FOR XML PATH('') ) as [To] from docSVsys with (nolock) order by docSVsys.sID for xml auto, root('documents') 

FOR XML PATH provides what I need and quickly. I tried a regular query and then generated the XML using Xdocument, but the performance is terrible even with more than 100 lines, since you need to add a To value to search in the sID. I think I could write concatenation as an SQL function to avoid FOR XML AUTO, but this query with FOR XML is fast and provides the exact results I need. This is how to get the result?

Answer 1

This is a known issue: http://support.microsoft.com/kb/310378

Use ExecuteXmlReader instead. The main reason is that SQL breaks the returned XML, so you need to read the answer in different ways. I had the same problem in ADO using VBScript.

PS I see how Tim answered with the same answer while I wrote.

Answer 2

Wrapping the whole result in an element seems to work for me, namely:

 select (select top 10 docSVsys.sID, docSVsys.docID , (select top 10 value + '; ' from docMVtext with (nolock) where docMVtext.sID = docSVsys.sID and docMVtext.fieldID = '113' order by value FOR XML PATH('') ) as [To] from docSVsys with (nolock) order by docSVsys.sID for xml auto, root('documents'))