title: Replace “&lt;” and “&gt;”and "&lt;\" with “<” ,“>” and </ in sql server
body: <p>I know that Replace <code>&amp;lt;</code> and <code>&amp;gt;</code> with <code>&lt;</code> and <code>&gt;</code> in sql server has been asked at </p>
  
  <p><a href="https://stackoverflow.com/questions/17503725/replace-lt-and-gt-with-and-in-sql-server">Replace “&amp;lt;” and “&amp;gt;” with “&lt;” and “&gt;” in sql server</a></p>
  
  <p>but I need to expand upon it.  I am trying to create formatted HTML code from a MS 2016 SQL server. </p>
  
  <p>What I have is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @json nvarchar(max) = (SELECT DISTINCT AU2.PMID, 
  SUBSTRING(
  (
   SELECT ', '+  CASE WHEN AU1.Format IS Not Null Then +'&lt;b&gt;' + 
   AU1.LastName+ ' ' + AU1.Initials + '&lt;/' + AU1.Format + '&gt;' ELSE
   AU1.LastName + ' ' + AU1.Initials END   AS [text()]
   FROM dbo.[Publication.PubMed.Author] AU1
   WHERE AU1.PMID = AU2.PMID
   ORDER BY AU1.PmPubsAuthorID
   FOR XML PATH ('')
  ), 3, 1000) + '. Journal Name ' [Authors]
  FROM dbo.[Publication.PubMed.Author] AU2
  WHERE AU2.[PMID] =27702896   FOR JSON AUTO)
  
  SELECT @json
  </code></pre>
  
  <p>And what I get back is:</p>
  
  <pre><code>Name DG, Name K, Name A, Chen FK, Song X, Pirun M, Santiago PM, Kim-Kiselak C, Platt JT, Lee E, Hodges E, &amp;lt;b&amp;gt;Rosebrock AP&amp;lt;/b&amp;gt;, Bronson RT, Socci ND, Hannon GJ, Jacks T, Varmus H Journal Name 
  </code></pre>
  
  <p>If I try to put TYPE into the </p>
  
  <pre class="lang-sql prettyprint-override"><code>FOR XML PATH(''), TYPE 
  </code></pre>
  
  <p>I get an error saying:</p>
  
  <blockquote>
    <p>Argument data type xml is invalid for argument 1 of substring function.</p>
  </blockquote>
  
  <p>I've tried to add <code>REPLACE</code> </p>
  
  <pre class="lang-sql prettyprint-override"><code>replace(CASE WHEN AU1.Format IS Not Null Then +'&lt;b&gt;' + 
  AU1.LastName+ ' ' + AU1.Initials + '&lt;/b&gt;' ELSE
  AU1.LastName + ' ' + AU1.Initials END,  '&amp;lt;', '&lt;') 
  </code></pre>
  
  <p>But then I get an error</p>
  
  <blockquote>
    <p>The xml data type cannot be selected as DISTINCT because it is not comparable.</p>
  </blockquote>
  
  <p>The best that I can come up with is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SET @json = replace(@json, '&amp;lt;\', '&lt;')
  SET @json = replace(@json, '&amp;lt;', '&lt;')
  SET @json = replace(@json, '&amp;gt;', '&gt;')
  </code></pre>
  
  <p>Just before the final <code>SELECT @json</code>.</p>
  
  <p>I will have over a 1000 records that I need to pull. Do I need to do this is steps? </p>
  
  <p>Based upon TT code I changed mine to </p>
  
  <pre><code>         DECLARE @json nvarchar(max) = (SELECT DISTINCT AU2.PMID, 
          SUBSTRING(
      (
          SELECT ', '+  CASE WHEN AU1.Format IS Not Null Then +'&lt;b&gt;' + 
           AU1.LastName+ ' ' + AU1.Initials + '&lt;/b&gt;' ELSE
           AU1.LastName + ' ' + AU1.Initials END   AS [text()]
          FROM dbo.[Publication.PubMed.Author] AU1
          WHERE AU1.PMID = AU2.PMID
          ORDER BY AU1.PmPubsAuthorID
          FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)'
      ), 1, 1000) + '. Journal Name ' [Authors]
        FROM dbo.[Publication.PubMed.Author] AU2
       WHERE AU2.[PMID] =27702896   FOR JSON AUTO)
  </code></pre>
  
  <p>And that woks fine. but see comment below</p>