c# - XML parsing: line 1, character 7, semicolon expected -

an error occurs when running xml sql in c#. missing? works fine in sql server management studio not in c#.

protected datatable servicecodes(string ordercolumn = default(string), bool orderascending = true) {     string sql = @" select distinct(split.a.value('.', 'varchar(100)')) data                                                               (                                  select cast('<m>' + replace(text09, ',', '</m><m>') + '</m>' xml) data                                     assets                                  category = 'service'                              ) cross apply data.nodes ('/m') split(a) order data";      return databaseconnection.executedatatable(sql, 120000); } 

i think there's special character in text09 column. try this:

select distinct(split.a.value('.', 'varchar(100)')) data     (  select cast('<m>' + replace((select text09 + '' xml path('')), ',', '</m><m>') + '</m>' xml) data     assets  category = 'service' ) cross apply data.nodes ('/m') split(a) order data 

see sql fiddle demo