.NET and TSQL "For Xml Auto" Splits XML Result

Question

I'm using some TSQL with 'For XML Auto'. If I run the TSQL in management studio, I get a single row. If I fill the DataTable using the DataAdapter.Fill() command, I get two results. It appears that the results are split in the middle of one of the tags. It is not a huge XML file, in fact its quite small (perhaps 20 lines), so size is likely not the issue. Here's the SQL.

SELECT Organization.Name, Organization.Abbreviation, Organization.WebsiteURL, Organization.AddressLine1, Organization.AddressLine2, Organization.City, Organization.Province, Organization.Country, Organization.PostalCode, Organization.Phone, Organization.Email, Organization.ContactFirstName, Organization.ContactLastName FROM
StudentLife.MissionWeekOrganization Organization INNER JOIN StudentLife.MissionWeekOrganizationAttendee reg ON Organization.MissionWeekOrganizationId = reg.MissionWeekOrganizationId WHERE (reg.AttendingYear = 2010 AND ReceivedDate IS NOT NULL) ORDER BY Organization.Name FOR XML AUTO

The results are split right in the middle of an element

... Organization Name=\"Samaritan's Purse Canada\" Abbreviation=\"\" WebsiteURL=\"http

then the second row in the DataTable picks up where it left off. Any ideas?

Answer 1

as I remember back when I used for xml auto some years ago, there was a character limit per row, that's why the engine is splitting up data to separate rows. I think you need to concatenate these fragments before you can use it as a source for any datatable.

Answer 2

Can you use "FOR XML AUTO, TYPE"? It would return a column of "XML" type.

Answer 3

That is the problem with sql server, it splits the result into multiple items when it thinks the results are big. You have to read the results and append to a string.

I don't know why this the behavior, but I was reading from a IDataReader and I was baffled. Later on I appended to a stringbuilder and got the output xml. I was getting a large xml and it was split 30 times.

Answer 4

Thanks for the suggestions guys. I figured it out. The problem isn't sql server (as I said management studio returns the answer properly in a single row). Adding Type doesn't help either, (Sql server knew that it was xml right from the start). The problem is that ADO.NET breaks up the results when creating the datatable.

It turns out that I shouldn't be using the SqlDataReader from Command.ExecuteReader. Instead I should be using the System.Xml.XmlReader from Command.ExecuteXmlReader. Then you do as you suggested, concatenating those results (although it probably would work to just concatenate all the results from all the rows using the DataAdapter.Fill method).

Thus something like this:

System.Xml.XmlReader rdr = Command.ExecuteXmlReader(); try { System.Text.StringBuilder sb = new System.Text.StringBuilder();

rdr.Read(); while (! rdr.EOF) { sb.Append(rdr.ReadOuterXml()); }

result = sb.ToString(); } finally { rdr.Close(); }

Thanks for the help!

Answer 5

Check this out: http://support.microsoft.com/default.aspx?scid=kb;en-us;310378

You can workaround in SQL (if that is an option) by assigning the xml result into a variable of type "xml" and returning this.

DECLARE @ResultXML xml

SET @ResultXML = 
 (SELECT ...
  FROM MyTable
  FOR XML AUTO)

SELECT @ResultXML as [MyXML]