title: Split distinct varchar data from one column into multiple rows
body: <p>How can I separate the values of the <code>CallTraversalLog</code> column into data per row. The result set must not contain any duplicates </p>
  
  <pre><code>SeqNum  CallId  DNI     CallTraversalLog
  94329   688     29636   CUSTSEG
  94329   688     29636   CUSTSEG;PHPM;CARDMNU;CRC;CRCACTIVATION;TPINCHANGE
  94332   696     29636   CUSTSEG;PHPM;CARDMNU;CRC;CRCACTIVATION;TPINCHANGE
  94333   699     29636   CUSTSEG;PHPM;CARDMNU;CRC;CRCACTIVATION;CRCENROLL
  94333   699     29636   CUSTSEG;PHPM;CARDMNU;CRC;CRCACTIVATION;CRCENROLL
  94333   702     29636   CUSTSEG;CARDMNU;CRC;CRCBISOA;CRCBI
  94334   703     29636   CUSTSEG;PHPM;CARDMNU;CRC;CRCACTIVATION;CRCENROLL
  94334   703     29636   CUSTSEG;PHPM;CARDMNU;CRC;CRCACTIVATION;CRCENROLL
  94334   706     29636   CUSTSEG;CARDMNU;CRC;CRCBISOA;CRCBI
  94336   710     29636   CUSTSEG;PHPM;PBLOGIN;PBMENU
  94340   714     29636   CUSTSEG;PHPM;PBLOGIN;PBMENU;PBBI;PBLST3
  </code></pre>
  
  <p>I am lost right now because I don't know how to accomplish this.</p>
  
  <p>The result should be like:</p>
  
  <pre><code>Code
  CUSTSEG
  PHPM
  PBLOGIN
  PBBI
  PBLST3
  CARDMNU
  CRC
  CRCACTIVATION
  CRCENROLL
  CRCBISOA
  CRCBI
  TPINCHANGE
  </code></pre>
  
  <p>I tried this solution but I get </p>
  
  <blockquote>
    <p>Msg 9411, Level 16, State 1, Line 1<br>
    XML parsing: line 1, character 37, semicolon expected</p>
  </blockquote>
  
  <p>The command I tried is:</p>
  
  <pre><code>SELECT DISTINCT 
      Split.a.value('.', 'VARCHAR(100)') data
  FROM   
      (SELECT 
          CAST('&lt;M&gt;' + Replace(CallTraversalLog, ';', '&lt;/M&gt;&lt;M&gt;') + '&lt;/M&gt;' AS XML) AS Data
       FROM   
          tblReportData) AS A
  CROSS APPLY 
      Data.nodes ('/M') AS Split(a) 
  </code></pre>
  
  <p>EDIT: I think I know why I get this error. Some records only contain special character <code>&amp;</code>. How can I substitute the special character to make the query work.</p>