Campaign Classic v7: Function unescapexmlvalue missing after database upgrade

The UnEscapeXmlvalue() function is missing after the database upgrade, causing workflows to throw errors. Learn how to resolve the issue.

Description description

Environment

Issue/Symptoms

The unEscapeXmlvalue() function is missing, and workflows are going into error after the upgrade.

17/06/2020 04:02:09 enrich22 matches the given name and argument types. You might need to add explicit type casts.

17/06/2020 04:02:09 enrich22 PGS-220000 PostgreSQL error: ERROR: function unescapexmlvalue(character varying) does not exist LINE 1: …W0.sDocumentNumber, W0.tsSchedulingContact, NULL, UnescapeXm… HINT: No function

Resolution resolution

Check the Postgres SQL script that has the OOB functions under Administration > Configuration > SQL scripts.

It’s generally the script.  xtk:postgresql-functions.sql

Locate the piece of SQL code that’s creating the missing function like:

– UnescapeXmlValue: unescape value coming from NodeValue

– NodeValue(‘/path’, '< path> value< /path> ‘) will return ‘< path> < ![ CDATA[ value] ] > < /path> ’
– this function will extract ‘value’ (casting the value from NodeValue in varchar will return
– ‘value’ in xml escaped form, and there is no easy way to unescape that using postgres)
create or replace function UnescapeXmlValue(text) returns text as ’
declare
pos int;
begin
pos = position(’’< ![ CDATA[ ‘’ in $1);
if pos > 1
then
– the value starts at pos + length(< !CDATA[ [ )
– length is total length minus:
– - size of starting tag (pos - 1);
– - size of closing tag (starting tag with / => pos);
– - size of < !CDATA[ [ (9) and ] ] > (3)
return substring($1 from pos + 9 for (char_length($1) - 2 * pos - 11));
end if;
return $1;
end;

Now create a new SQL function under SQL scripts with only this piece of SQL statement and execute it.

This should create the missing function in the database again.

Note: Do not run the entire SQL script : postgresql-functions.sql  again, as some existing functions might drop.

The same solution can be applied to other missing OOB functions as well.

Cause

It might be caused by an incomplete or corrupt database engine upgrade.