So I've inherited this database with some invalid xml data in one table.
I know for a fact that the character is a control char and I can find it in the column (which is of type XML):
The statement fails with message: "XML parser error: character: 603, line:1, column:603 Illegal control character SQLCODE=-888, ODBC 3 State="HY000"
Any ideas how to get rid of that character? (Running SQLA 12)
Looks like Volker's suggestion to first update the BLA_COLUMN to a Null value then to update to a valid XML, replacing char(26) with char (32), provides a workaround. Thanks Volker!
Is the same true if you try to set the column to null (which would look like the previous contents is parsed, too)...?
EDIT: As this seems to have done the trick, it shows that
I think you need to convert the xml data to something that SQLA doesn't try to interpret.
create variable mytext long varchar; set mytext = (SELECT (BLA_COLUMN) FROM BLA_TABLE where ID_RECORD = 1234); set mytext= REPLACE(mytext, char(26), char(32)); update BLA_TABLE set BLA_COLUMN = mytext where ID_RECORD = 1234; commit;
If you have to do this a lot you could write a user defined function. Also if you have characters which have problems because of collation tables eg
update person set notes=replace(notes,'ú','£')and every u in the field will also be changed as the collation sequence helpfully reckons that 'ú' is the same as 'u', you can use this approach:
create function BinaryReplace(in x long varchar,in targetascii smallint, in replacementascii smallint) returns long varchar deterministic begin declare rv long varchar; declare l integer; declare i integer; declare c char(1); set l=length(x); set i=0; set rv=''; while i < l loop set i=i+1; set c=substr(x,i,1); if ascii(c) = targetascii then set c=char(replacementascii) end if; set rv=rv+c end loop; return rv end;
not fast, but quicker than typing!