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
&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;
&lt;PARAM&gt;
  &lt;TAB DIM=&quot;30&quot; ID=&quot;ZC3D2_1&quot; SIZE=&quot;5&quot;&gt;
    &lt;LIN NUM = &quot;1&quot;&gt;
      &lt;FLD NAME = &quot;ZDOC&quot; TYPE = &quot;Char&quot;&gt;Ferran López&lt;/FLD&gt;
    &lt;/LIN&gt;
  &lt;/TAB&gt;
&lt;/PARAM&gt;

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 &lt;xml&gt; tag, or at least, the encoding attribute.

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

Question
Is there a way to convert it to XML without replacing or removing &lt;xml&gt; tag?
CAST(REPLACE(data, 'encoding=&quot;UTF-8&quot;', '') as XML)

db&lt;&gt;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.
&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;
&lt;PARAM&gt;
  &lt;TAB DIM=&quot;30&quot; ID=&quot;ZC3D2_1&quot; SIZE=&quot;5&quot;&gt;
    &lt;LIN NUM = &quot;1&quot;&gt;
      &lt;FLD NAME = &quot;ZDOC&quot; TYPE = &quot;Char&quot;&gt;Ferran LÃ³pez&lt;/FLD&gt;
    &lt;/LIN&gt;
  &lt;/TAB&gt;
&lt;/PARAM&gt;

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.