Umbraco Xml Exception – Error Publishing Invalid character

A general problem that I recently encountered for the second time is an Umbraco site not being able to generate any page without displaying an error or timeout. After examining the log (umbracoLog table on SQL server) you will see the error below:

  Error Republishing: System.Xml.XmlException: '', hexadecimal value 0x1F, is an invalid character. Line 1, position 2166.
   at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type, Int32& outStartPos, Int32& outEndPos)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type)
   at System.Xml.XmlTextReaderImpl.ParseElementContent()
   at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)
   at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at umbraco.content.LoadContentFromDatabase()

This means that somehow (usually if you copy/paste text from other sources directly to the editor) an invalid character has been inserted on a node. That invalid character prevents the XML document created by Umbraco to be loaded and therefore no pages can be generated. To find which node has the error you can use the code below:

DECLARE @invalidCharacter nvarchar(100) = convert(nvarchar(100), 0x1F)

SELECT * FROM [cmsContentXML]
WHERE
	xml like '%'+@invalidCharacter+'%'

Now that you know where the problematic character is you can replace it either using Umbraco administrator or using the T-SQL below:

DECLARE @invalidCharacter nvarchar(100) = convert(nvarchar(100), 0x1F)

UPDATE [cmsPropertyData] SET
	[dataNvarchar] = REPLACE(cast([dataNvarchar] as nvarchar(max)),@invalidCharacter,''),
	[dataNtext] = REPLACE(cast([dataNtext] as nvarchar(max)),@invalidCharacter,'')
WHERE
	[dataNvarchar] like '%'+@invalidCharacter+'%' OR
	[dataNtext] like '%'+@invalidCharacter+'%'

UPDATE [cmsContentXML] SET
	[xml] = REPLACE(cast([xml] as nvarchar(max)),@invalidCharacter,'')
WHERE
	[xml] like '%'+@invalidCharacter+'%'

Depending on your error you need to change the hexadecimal value that creates the problem. In my case and the examples above I have used “0x1F“. This value is at the first line of the error message “.. , hexadecimal value 0x1F, …”.