When running an Automation task which runs a SQL statement that has XML and CDATA tags inside it, the following error appears during the Automation script run:
'System.Exception - Export_1 - Cannot have ']]>' inside an XML CDATA block'
The error doesn't occur in Heidi when it is run.
Issue related to code, not Toad Data Point. This error happens due to the ]] characters in the CDATA line(s) in the SQL file.
This syntax causes Toad Data Point (TDP) to show this error, because it does some XML validation and does not in Heidi.
An example line in the SQL file such as: ELSE CONCAT('<Name><![CDATA[', table.colName, ']]></Name>') becomes invalid when run in Toad because it does XML validation and as the ']]' characters are in the line, but there is more text after it, it makes it invalid. It is only valid if table.colName doesn't contain ]]
Heidi returns a string such as: <Name><![CDATA[some text ]]> more text]]></Name> but doesn't try to validate the XML, which is why there is no error there.
WORKAROUND 1:
Do not use CDATA in the SQL query
WORKAROUND 2:
Modify the CONCAT part of the statement, e.g.:
ELSE CONCAT( '<Name><![CDATA[', REPLACE(table.colName, ']]>', ']]]]><![CDATA[>'), ']]></Name>')Show more lines
This converts:
]]>
into:
]]]]><
