Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Dbamp Error' Column CARS_Note__c contains invalid XML characters.'

Hi
While Loading some data in SF using BulkOps procedure . I ran into this error
'Error: Column contains invalid XML characters.'
I checked in the column value . We do not have : &, < and >, " or ' in the string.

Please advise.
2 people have
this problem
+1
Reply
  • DBAmp throws this error when there is a character in the input column data with a value below decimal 32. The only exceptions allowed are tab (dec 9), newline (dec 10) or CR (dec 13).

    Basically you want to replace all characters in the data of the column below decimal 32 (space) with a blank. You could use SQL to print the nvarchar string as hex and then scan the field for the troublesome characters. See http://blogs.visigo.com/chriscoulson/... for an example on how to do this.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • I received the same error message and found a char 5 in the data. Removing the char stopped the error.

    But I found that using:
    EXEC SF_BulkOps 'UPSERT:BulkApi(10000),parallel','SALESFORCE','table_name__c_upsert','externaId__c'

    I received no error messages in the Error column (it was null for all rows).

    Using:
    EXEC SF_BulkOps 'UPSERT','SALESFORCE','table_name__c_upsert','externaId__c'

    Gave the same error message, but the Error fields were filled in correctly.
    We also saw a table_name__c_upsert_Result table get created, but it also had null in the error column.

    Here is the function I used to remove the bad characters:


    CREATE FUNCTION removeInvalidUnicode(@nstring AS nvarchar(MAX))
    RETURNS nvarchar(MAX)
    AS
    BEGIN
    DECLARE @position INT, @rstring nvarchar(max);
    SET @position = 1;
    SET @rstring = '';
    WHILE @position < 32
    AND UNICODE(SUBSTRING(@nstring, @position, 1)) <> 9
    AND UNICODE(SUBSTRING(@nstring, @position, 1)) <> 10
    AND UNICODE(SUBSTRING(@nstring, @position, 1)) <> 13))
    BEGIN
    SET @rstring = @rstring + SUBSTRING(@nstring, @position, 1)
    END;
    SELECT @position = @position + 1;
    END
    RETURN @rstring;
    END

    go
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited

  • Here is a function that does what the above was intended to do, plus allows you to specify a substitution character:
    CREATE FUNCTION removeInvalidUnicode(@nstring AS nvarchar(MAX), @replaceWith AS nvarchar(1))  
    RETURNS nvarchar(MAX)
    AS
    BEGIN
    DECLARE @position INT;
    DECLARE @rstring nvarchar(max);
    DECLARE @s nvarchar(1);
    DECLARE @univalue INT;
    SET @position = 1;
    SET @rstring = '';

    WHILE @position <= LEN(@nstring)
    BEGIN
    SET @s = SUBSTRING(@nstring, @position, 1);
    SET @univalue = UNICODE(@s);
    IF ((@univalue < 32) AND (@univalue not in (9, 10, 13)))
    SET @rstring = @rstring + @replaceWith;
    ELSE
    SET @rstring = @rstring + @s;

    SET @position = @position + 1;
    END

    RETURN @rstring;
    END

    go

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Herb, Thanks for noticing the bug and posting a new function. I truly appreciate your contribution to the DBAmp community.

    Bill
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated