Error converting VARCHAR(MAX) to XML due to "UTF-8" attribute

I need to dig into a logs table with a schema similar to this:

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

Column data stores a XML text received from a web service in this format:

This is a reduced version

  
    
      Ferran López
    
  

When I try to cast this text to XML I get next error:

XML parsing: line xx, character 48, illegal xml character

It can be solved by removing the ` tag, or at least, the encoding attribute.

NOTE: It works fine if there is no special characters like ó, even if I don't remove tag.

Question

Is there a way to convert it to XML without replacing or removing ` tag?

CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>fiddle here

UPDATE

The server collation is: Latin1_General_BIN

But even if I try to change the collation to my usual servers collation, it doesn't work.

SELECT
  id, 
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;

Your XML stored in a varchar(max) column should look like this.

  
    
      Ferran López
    
  

The ó should be represented with a double byte value ó.

If you don't have a UTF-8 encoded string stored in your column, the right way to go about this is to remove the encoding from the XML before you convert the value to the XML datatype.

StackExchange Database Administrators Q#278684, answer score: 7