DECLARE

@s-2 VARCHAR(MAX)

,@Split1 CHAR(1)

,@Split2 CHAR(1)

,@Split3 CHAR(1)

,@X XML

/* User input */

SELECT

@s-2 = '1-32|26,42-41|32'

,@Split1 = ','

,@Split2 = '-'

,@Split3 = '|'

/* Split the string and convert to XML */

SET @s-2 = '<Question><QuestionID>'+REPLACE(@S,@Split1,'</Value></Question><Question><QuestionID>')

SET @s-2 = REPLACE(@S,@Split2,'</QuestionID><IndexNum>')

SET @s-2 = REPLACE(@S,@Split3,'</IndexNum><Value>')+'</Value></Question>'

SELECT @X = CONVERT(XML,'<root>' + @s-2 + '</root>')

/* You could stop here and just display the data as XML */

SELECT @X

/* Display the results in a table */

DECLARE @XML_Temp TABLE (XML_Content XML)

INSERT INTO @XML_Temp VALUES (@X)

SELECT

Node.value('(QuestionID)[1]', 'varchar(50)') AS 'QuestionID'

,Node.value('(IndexNum)[1]', 'varchar(50)') AS 'IndexNum'

,Node.value('(Value)[1]', 'varchar(50)') AS 'Value'

FROM

@XML_Temp

CROSS APPLY

@X.nodes('root/Question/.') AS Content(Node)

/* Output:

QuestionId IndexNumber Value

1 32 26

42 41 32

*/