Getting the error " XML parsing: illegal qualified name character"

Other SQL Server Topics
1

My requirement :
Source :
Answer_Choice

X;0;Y;0;Z

Result :
Answer_Request Rn

X 1
Y 3
Z 5

Query I used:

with cte_row as
(
SELECT row_num,
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)
FROM survey
GROUP BY answer_choices) AS a
CROSS APPLY String.nodes ('/M') AS Split(a)),
cte_num AS
(SELECT rn = ROW_NUMBER() over(partition by row_num order by row_num),*
FROM cte_row)
SELECT rn
,answer_request
FROM cte_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???

2

post xml here or post sample data

3

Answer Choice :
Predominant negative symptoms of schizophrenia;0;Bipolar depression;0;Cognition;0;Compliance;0;Metabolic tolerability;0;Other;0

(For above record, query worked fine...)

Patient has been migraine free (> 6 months);0;Tolerability;0;Cost;0;Pregnancy/lactation;0;Change in efficacy/perceived efficacy by patient;0;Has not had to discontinue patients;0;Other;0

For this record,query was returning an error..

4

please provide sample data using ddl and and dml

create table #sample(x vqrchar(50))

insert into #sample
select '>a:6,b:f'