Read Large XML String from TSQL FOR XML Statement in C# .NET

Question

Have a SQL command with FOR XML that returns a single long XML as SqlString. My problem is reading the long XML string into .NET C#.

The following only reads the first 2033 characters

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

I have also tried command.ExecuteScalar and rdr.GetString and still only get the first 2033 characters. I have changed the sort and it still truncates at 2033 so it is not likely caused by a bad character. Tried rdr.GetSqlXml and get an error message cannot cast SqlString to SqlCachedBuffer.

If I limit the SQL to return less than 2033 character I do get the complete valid XML. So I don't think it is an XML parsing issue rather just a truncation. I don't need any XML parsing - it is valid XML from the TSQL statement that I need as string.

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

The 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')

The FOR XML PATH provides what I need and fast. I tried a regular query and then generating the XML using Xdocument but performance is horrible with even more than 100 lines as it needs to search on sID to add the To. I guess I could write the concatenation as a SQL function to avoid the FOR XML AUTO but that query with the FOR XML is fast and provides the exact results I need. It is just how to retrieve the result?

Answer 1

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

Use ExecuteXmlReader instead. The underlying cause is that SQL breaks up the returned XML, so you need to read the reply differently. I've had the same issue in ADO using VBScript too.