Many application have functionality for importing or exporting data in XML format. Sometimes you will get an error about an illegal XML character:
XML parsing: line 1, character 2967663, illegal xml character
And then you will have to try to find that character, which is very hard. This solution can help you out.
This solution creates a stored procedure search_illegal_xml_characters which searches your entire database for illegal XML characters. It is also possible to search in one specific table.
The procedures uses a function, remove_illegal_characters_for_xml, which is also created in this solution. Important: this function does NOT remove illegal characters from your database. It only removes those characters from a string.
This dynamic model code creates stored procedure search_illegal_xml_characters and function remove_illegal_characters_for_xml.
If you want to use this procedure as task, create your own task which start the procedure.
This control procedure adds a template to function remove_illegal_characters_for_xml.
This template provides code for function remove_illegal_characters_for_xml.
This control procedure adds three templates to procedure search_illegal_xml_characters.
This template declares needed variables.
This template searches in a field for an illegal XML character.
This template prints the results to the screen.
In the attachment you will find the code of this solution.