My requirement :
Query I used:
with cte_row as
answer_request = Split.a.value('.', 'VARCHAR(1000)')
FROM (SELECT row_num = ROW_NUMBER() over(order by answer_choices),
String = CAST ('' + REPLACE(answer_choices, ';', '') + '' AS XML)
GROUP BY answer_choices) AS a
CROSS APPLY String.nodes ('/M') AS Split(a)),
(SELECT rn = ROW_NUMBER() over(partition by row_num order by row_num),*
WHERE answer_request <> '0'
This query is failing when I get the source row as : A>1;B;C (> in the input data)..
Error : XML parsing: illegal qualified name character
Can any one help out on this please???