CODEWITHSUNDEEP
sql-serverxmlt-sql
paparazzo
paparazzo

Reputation: 45096

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

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?

Upvotes: 6

Views: 5693

Answers (2)

Chris
Chris

Reputation: 439

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.

Upvotes: 11

user5789182
user5789182

Reputation: 1

Wrapping the whole result in a select seems to work for me i.e.:

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

Upvotes: 0

Related Questions