title: Msg 9402, Level 16, State 1, Line 9 XML parsing: line 1, character 38, unable to switch the encoding
body: <p>A piece of tsql code doesnt behave the same from production to Test environment. When the code below is executed on prod it brings back data</p>
  
  <pre><code>SELECT [col1xml]
  
  FROM [DBName].[dbo].[Table1] (NOLOCK)
  
  WHERE (cast([col1xml] as xml).value('(/Payment/****/trn1)[1]','nvarchar(20)') ='123456'))
  </code></pre>
  
  <p>However that same code brings back the below error when ran in Test.</p>
  
  <blockquote>
    <p>Msg 9402, Level 16, State 1, Line 9
    XML parsing: line 1, character 38, unable to switch the encoding</p>
  </blockquote>
  
  <p>I have seen the fix provided by this site of conversion of UTF and this works in both prod and test. See below. However i need to provide an answer to the developers of why this behavior is occurring and a rationale why they should change their code(if that is the case)</p>
  
  <pre><code>WHERE CAST(
  
  REPLACE(CAST(col1xml AS VARCHAR(MAX)), 'encoding="utf-16"', 'encoding="utf-8"')
  
  AS XML).value('(/Payment/****/trn1)[1]','NVARCHAR(max)') ='123456')
  </code></pre>
  
  <p>I have compared both DB's and looked for anything obvious such as ansi nulls and ansi padding. Everything is the same and the version of SQL Server. This is SQL SERVER 2012 11.0.5388 version. Data between environments is different but the table schema is identical and the data type for col1xml is ntext.</p>