title: Escape XML special characters upon convert
body: <p>I have working <strong>csv</strong> splitter for my needs.</p>
  
  <p>You can just grab and run it as is:</p>
  
  <pre><code>declare @t table(data varchar(max))
  insert into @t select 'a,b,c,d'
  insert into @t select 'e,,,h'
  
  ;with cte(xm) as 
  (
      select convert(xml,'&lt;f&gt;&lt;e&gt;' + replace(data,',', '&lt;/e&gt;&lt;e&gt;') + '&lt;/e&gt;&lt;/f&gt;') as xm 
      from @t
  )
  select
      xm.value('/f[1]/e[1]','varchar(32)'),
      xm.value('/f[1]/e[2]','varchar(32)'),
      xm.value('/f[1]/e[3]','varchar(32)'),
      xm.value('/f[1]/e[4]','varchar(32)')
  from cte
  </code></pre>
  
  <p>Only issue is, that if I introduce an XML sensitive character in the <strong>data</strong>, like <strong>&amp;</strong>:</p>
  
  <pre><code>insert into @t select 'i,j,&amp;,k'
  </code></pre>
  
  <p>It fails with error: character 24, illegal character</p>
  
  <p>One solution is to replace <strong>&amp;</strong> character to <strong>&amp;amp</strong> on the fly, like this:</p>
  
  <pre><code>select convert(xml,'&lt;f&gt;&lt;e&gt;' + replace(replace(data,'&amp;','&amp;amp'),',', '&lt;/e&gt;&lt;e&gt;') + '&lt;/e&gt;&lt;/f&gt;') as xm 
  </code></pre>
  
  <p>but there are several dozens of special XML characters which I need to escape upon convert, and I can't really nest dozens <strong>replace(replace(replace(...</strong> functions in there. That's what i did and it is messy.</p>
  
  <p>How the above code can be modified to escape XML sensitive characters, and produce the same result?</p>
  
  <p>Thanks!</p>