Given:   "Title,Forenames,Surname"

Return:
  ID     Value
  ------ ----------------
  1      Title
  2      Forenames
  3      Surname

Note the below example omits the ID column and just leaves VALUE.

1.  Given:   "Title,Forenames,Surname" 
2.   
3.  Return: 
4.    ID     Value 
5.    ------ ---------------- 
6.    1      Title 
7.    2      Forenames 
8.    3      Surname 
9.   
10.  Note the below example omits the ID column and just leaves VALUE. 

DECLARE @myStringToParse VARCHAR(max), @myXML XML;
SET @myStringToParse = 'Title,Forenames,Surname';
SELECT @myXml = CONVERT(xml,'<root><s>' + REPLACE(@myStringToParse,',','</s><s>') + '</s></root>');
SELECT [Value] = T.c.value('.','varchar(20)') FROM @myXml.nodes('/root/s') T(c);

1.  DECLARE @myStringToParse VARCHAR(max), @myXML XML; 
2.  SET @myStringToParse = 'Title,Forenames,Surname'; 
3.  SELECT @myXml = CONVERT(xml,'<root><s>' + REPLACE(@myStringToParse,',','</s><s>') + '</s></root>'); 
4.  SELECT [Value] = T.c.value('.','varchar(20)') FROM @myXml.nodes('/root/s') T(c);