Split String with Multiple Delimiters (T-SQL XML Method)

http://forums.asp.net/p/1852168/5184467.aspx/1?p=True&t=634861565202699866

--declare @Questions varchar(100)='1-32|26,42-41|32'
declare @Questions varchar(100)='1-32-26,42-41-32,1-46-1'

DECLARE @myXML AS XML  = N'<H><r>' + Replace(@Questions, ',', '</r><r>') + '</r></H>'

;WITH cte

AS (
SELECT CAST(N'<H><r>' + replace(Replace(Vals.id.value('.', 'NVARCHAR(50)'),'-','|'), '|', '</r><r>') + '</r></H>' as XML) AS val

FROM @myXML.nodes('/H/r') AS Vals(id) )

SELECT DISTINCT S.a.value('(/H/r)[1]', 'NVARCHAR(50)') AS QuestionId,
S.a.value('(/H/r)[2]', 'NVARCHAR(50)') AS [IndexNumber],
S.a.value('(/H/r)[3]', 'NVARCHAR(50)') AS Numbers
FROM cte CROSS APPLY val.nodes('/H/r') S(a);

Another sample:

declare @test table(id int identity(1,1), myString varchar(35) );
insert into @test values('\string1\string2\file0123.xml.xxx');

SELECT distinct  S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + Replace(REPLACE(myString, '\', '</r><r>'),'.','</r><r>') + '</r></H>' AS XML) AS [vals]
FROM @test) d

CROSS APPLY d.[vals].nodes('/H/r') S(a)

---Or

declare @myString varchar(100)='\string1\string2\file0123.xml.xxx'

;WITH cte
AS (
SELECT CAST(N'<H><r>' + Replace(REPLACE(@myString, '\', '</r><r>'),'.','</r><r>') + '</r></H>' AS XML) AS val
 )

SELECT DISTINCT S.a.value('(/H/r)[4]', 'NVARCHAR(50)') AS val
FROM cte CROSS APPLY val.nodes('/H/r') S(a);

One more example:

create table test(id int,resp varchar(100))
insert into test(Id,resp)
SELECT 1,'1^123,2^321,3^234,4^321' UNION
SELECT 2,'1^321,2^123,3^345,4^543'
--select * from test

 ;with mycte as
 ( SELECT  id,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 FROM( SELECT *,CAST (N'<H><r>' + Replace( resp, ',','</r><r>') + '</r></H>' AS XML) AS [vals]
 FROM test) d   CROSS APPLY d.[vals].nodes('/H/r') S(a)   
 )
 Select DISTINCT id, S.a.value('(/H/r)[1]', 'VARCHAR(100)') qn,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') ans  
 FROM (Select *, CAST (N'<H><r>' + Replace( splitVal1, '^','</r><r>') + '</r></H>' AS XML) [vals] from mycte) d   
 CROSS APPLY d.[vals].nodes('/H/r') S(a)   
drop table test
/*
id	qn	ans
1	1	123
1	2	321
1	3	234
1	4	321
2	1	321
2	2	123
2	3	345
2	4	543
*/

---