DECLARE @str VARCHAR(MAX);
SET @str = '[Mr/Mrs XuserX from XLocationX has created a new bill.
<br><br><b>Bill Details</b><table border=0><tr><td>Amount:</td>
<td>XAmountX</td></tr><tr><td>Vendor Name:</td><td>XVendorX</td></tr>
<tr><td>Payment Due Date:</td><td>XDueDateX</td></tr><tr><td>Remarks:
</td><td>XRemarksX</td></tr></table><table width="80%"><tr><td><p 
style="text-align:justify;">The information contained in this e-mail
 message and any files/attachment transmitted with itis confidential
 and for the sole use of the intended recipient(s) or entity 
identified. If you arenot the intended recipient, please contact us 
at Technology@impetusconsulting.net and destroy/delete allcopies and
 attachment thereto along with the original message.</p></td></tr>
</table>]';

;WITH cte(id , loc ) as
(
   SELECT 1, CHARINDEX('X',@str,1) 
   UNION ALL
   SELECT
      c.id+1,
      CHARINDEX('X',@str,c.loc+1)
   FROM
      cte c
   WHERE
      CHARINDEX('X',@str,c.loc+1) > 0
)
SELECT
   a.id,b.id,
   SUBSTRING(@str,a.loc,b.loc-a.loc+1)
FROM
   cte a
   INNER JOIN cte b ON b.id = a.id+1
WHERE
   a.id%2 = 1;

;WITH cte(Id,RowId , loc ) as
(
   SELECT Id,1, CHARINDEX('X',Content,1) 
   FROM MyTable
   UNION ALL
   SELECT
      t.Id,c.RowId+1,
      CHARINDEX('X',Content,c.loc+1)
   FROM
      cte c join MyTable t ON c.Id = t.Id
   WHERE
      CHARINDEX('X',Content,c.loc+1) > 0
)

SELECT Id,
   STUFF((	SELECT ', ' + SUBSTRING(Content,a.loc,b.loc-a.loc+1) 
			FROM
			   cte a
			   INNER JOIN cte b ON b.RowId = a.RowId+1
			   INNER JOIN MyTable t ON t.Id = t1.Id AND a.Id = t.Id AND b.Id = t.Id
			WHERE
			   a.RowId%2 = 1
			FOR XML PATH (''))  ,1,1,''
		) AS Content
FROM  MyTable t1