title: How to entity encode delimiter-in-value when concatenating using T/SQL FOR XML trick
body: <p>I'm using the T/SQL FOR XML trick to concatenate a set of values with a delimiter. I'd like to entity encode my delimiter (| in this case) within the values, but then the FOR XML re-encodes the &amp;#124; so I end up with &amp;amp;#124;</p>
  
  <pre><code>if object_id('tempdb..#t') is not null drop table #t
  create table #t (v varchar(100))
  insert #t(v) values ('Foo'),('A|B'),('Bar')
  
  select (
      select '|' + v AS [text()]
      FROM #t
      FOR XML PATH ('')
  ) + '|' as vConcat
  
  -- Result of above = |Foo|A|B|Bar| -- delimiter in A|B not escaped
  
  select (
      select '|' + replace(v, '|', '&amp;#124;') AS [text()] 
      FROM #t
      FOR XML PATH ('')
  ) + '|' as vConcat
  
  -- Result of above = |Foo|A&amp;amp;amp;#124;B|Bar| -- delimiter double-escaped
  </code></pre>
  
  <p>Is there a way to get FOR XML to encode additional characters? Or is there a better way of achieving escaped delimiters inside FOR XML?</p>
  
  <p>Using a different delimiter is not the answer I'm looking for.</p>
  
  <p>I know that wrapping the whole expression in a replace to undo the double encoding will work, but it doesn't strike me as very elegant.</p>
  
  <pre><code>select replace((
      select '|' + replace(v, '|', '&amp;#124;') AS [text()] 
      FROM #t
      FOR XML PATH ('')
  ) + '|', '&amp;amp;#124;', '&amp;#124;') as vConcat
  
  -- Result of above = |Foo|A&amp;#124;B|Bar|
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>To make the question clearer, I want to entity encode my delimiter (within original values only) <em>in addition to</em> the usual special XML characters. </p>
  
  <pre><code>if object_id('tempdb..#t') is not null drop table #t
  create table #t (v varchar(100))
  insert #t(v) values ('F&amp;o'),('A|B'),('Bar'),('A&amp;#124;B')
  
  select (
      select '|' + v AS [text()]
      FROM #t
      FOR XML PATH ('')
  ) + '|' as vConcat
  
  -- Result of above = |F&amp;amp;o|A|B|Bar|A&amp;amp;#124;B| -- special chars encoded but not delimiter in A|B value
  
  select (
      select '|' + replace(v, '|', '&amp;#124;') AS [text()] 
      FROM #t
      FOR XML PATH ('')
  ) + '|' as vConcat
  
  -- Result of above = |F&amp;amp;o|A&amp;amp;#124;B|Bar|A&amp;amp;#124;B| -- delimiter in A|B value double-encoded
  
  -- Desired result = |F&amp;amp;o|A&amp;#124;B|Bar|A&amp;amp;#124;B|
  </code></pre>