FOR XML cutting off data

• tshad

  SSCertifiable

  Points: 5905

  More actions

  December 3, 2011 at 11:08 am

  #260858

  I have a table that I am trying to query using FOR XML. The problem is that when I do it to text or to a file it cuts off the data.

  SELECT TOP 2 *

  FROM dbo.ClickFormsCheckboxPairs

  Gives me:

  ClickFormsCheckboxPairID Name UseTypeName InsertID InsertDate LastUpdateID LastUpdateDate ConcurrencyID

  ------------------------ ---------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------- ----------------------- -------------------------------------------------- ----------------------- -------------

  1 UtilitiesAndOffSiteImprovementsConformToNeighborhood 1 NULL 2011-10-03 13:31:28.827 NULL 2011-10-03 13:31:28.827 1

  2 AdverseSiteConditions 1 NULL 2011-10-03 13:31:51.020 NULL 2011-10-03 13:31:51.020 1

  (2 row(s) affected)

  But

  SELECT TOP 2 *

  FROM dbo.ClickFormsCheckboxPairs

  FOR XML auto

  Gives me:

  XML_F52E2B61-18A1-11d1-B105-00805F49916B

  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  <dbo.ClickFormsCheckboxPairs ClickFormsCheckboxPairID="1" Name="UtilitiesAndOffSiteImprovementsConformToNeighborhood" UseTypeName="1" InsertDate="2011-10-03T13:31:28.827" LastUpdateDate="2011-10-03T13:31:28.827" ConcurrencyID="1"/>

  <dbo.ClickFormsCheckboxPa

  (2 row(s) affected)

  The second row is cut off. If I were to do this without the "Top" statement, I would get mor data but much would be incomplete (missing data, missing parts of tags, etc).

  What would cause this?

  I am running this from a query window. It happens whether I write the results of the query to text or to a file.

  Thanks,

  Tom

• tshad

  SSCertifiable

  Points: 5905

  More actions

  December 3, 2011 at 11:14 am

  #1416334

  I found out why it does this if I have the Query window results set to Text.

  From Query/Query Options/Text, I have it set to 256.

  But why would that do the same for writing to a file?

  If I want to write out the whole table in xml format, how would I do that? It would be way larger than 8192.

  Thanks,

  Tom