Remove illegal char from xml column

[Breck_Carter]

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):

SELECT CHARINDEX(char(26), BLA_COLUMN) FROM BLA_TABLE where ID_RECORD = 1234 Returns a valid index. Now, when I try to replace that char with something else:

UPDATE BLA_TABLE SET BLA_COLUMN = REPLACE(BLA_COLUMN, char(26), char(32)) WHERE ID_RECORD = 1234

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)

Thank you!

EDIT

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!

[Breck_Carter]

Also untested...

UPDATE BLA_TABLE 
   SET BLA_COLUMN = REPLACE ( CAST ( BLA_COLUMN, LONG VARCHAR ), 
                              char(26), char(32)) 
 WHERE ID_RECORD = 1234

[Former Member]

Same error as above unfortunately

[justin_willey]

Do you have a support contract with SAP - it's beginning to look like you may need to open a case.

But in the meantime: The docs say: You can cast between the XML data type and any other data type that can be cast to or from a string. Note that there is no checking that the string is well-formed when it is cast to XML.

So it's looking a bit like you can write in anything, but can only read out well formed XML.

To check this can you try just selecting the XML into a string without trying to write anything back - I'm just wondering if there is more than one illegal character which is why Breck's method fails.

ie something like

create variable mytext long varchar;
set mytext = (SELECT (BLA_COLUMN) FROM BLA_TABLE where ID_RECORD = 1234);

OR

create variable mytext long varchar;
set mytext = (SELECT cast(BLA_COLUMN as long varchar) FROM BLA_TABLE where ID_RECORD = 1234);

and see if you can then read what is in mytext.

[Former Member]

Reading works fine in both cases. The updating with a valid XML proved to be tricky. (Please see my Edit)

[justin_willey]

I think you need to convert the xml data to something that SQLA doesn't try to interpret.

eg (untested)

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!

[Former Member]

Weird! I can't even run a statement like: UPDATE BLA_TABLE set BLA_COLUMN = '<bla/>' where ID_RECORD=1234; without the XML parser complaining as above

[justin_willey]

Is Breck's method allowed to work without the parser complaining?