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?

Also, this is essentially the same as: stackoverflow.com/questions/5423980/… — Tim Lehner
– Tim Lehner, Commented Dec 12, 2011 at 16:29

ΩmegaMan · Accepted Answer · 2018-06-07 15:28:01Z

11

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.

edited Jun 7, 2018 at 15:28

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

answered Dec 12, 2011 at 16:25

Chris

4397 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Lehner Over a year ago

But you could use the rep a slight bit more than me!

user5789182 · Accepted Answer · 2016-01-14 10:37:23Z

0

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

answered Jan 14, 2016 at 10:37

user5789182

1

1 Comment

Chris Over a year ago

This doesn't apply to the OP's question. The issue is not that the SQL didn't return the correct XML, the issue is that when your SQL query returns a large XML string it gets chopped up into sections of 2033 characters by the .NET framework. If you don't read it the right way you never get more than the first 2033 characters.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related