sql server FOR XML has a character limit?

Question

I'm executhing a query in xml and converting it into an xml file with FOR XML EXPLICIT. And this works, unless of I try to query a lot for files.

If I query the records with IDs between 400 and 500 it works if I try to query the records with IDs between 500 and 600 it works but if I try to query 400 to 600 it fails.

This is the error:

Unable to show XML. The following error happened: '', hexadecimal value 0x1E, is an invalid character. Line 1457, position 107.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

Okay? and then what? Where do I have to make the change in options exactly?

Edit: Setting XML Data to unlimited under the Query results > SQL Server > Results to grid to unlimited does not work.

Edit: returned with code:

<ExceptionDocument><Error><Message>'&#x1E;', hexadecimal value 0x1E, is an invalid character. Line 953, position 107.</Message><StackTrace>   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
   at System.Xml.XmlTextReaderImpl.ThrowInvalidChar(Char[] data, Int32 length, Int32 invCharPos)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type, Int32&amp; outStartPos, Int32&amp; outEndPos)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type)
   at System.Xml.XmlTextReaderImpl.ParseElementContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)
   at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at SitePublisher.Base.BasePub.ExecuteCommand(String sql, Int32 attempt, NameValueCollection parameters)</StackTrace><InnerException /></Error></ExceptionDocument>

Edit: I'm guessing 0x1E is an end of file character.

Edit: I think my first statement might have been wrong and that I'm dealing with corrupt data. It always returns an error for record 426.

Answer 1

Necromancing.

In SQL-Server Management-Studio, go to Tools->Options

Under QueryResults->SQL-Server->"Results To Grid" set the value of "XML data" to unlimited.
Now you need to restart SQL-Server Management Studio for the changes to take effect.

Answer 2

It apparently was indeed an invalid character, it showed up as 'RS' in notepad++, The error that the SQL managment studio gave totally set me off track.