XML parsing: line 1, character 7, semicolon expected

Question

An error occurs when running this XML SQL in C#. What is missing? It works fine in SQL Server management studio but not in the c#.

protected DataTable ServiceCodes(string orderColumn = default(string), bool orderAscending = true)
{
    string sql = @" SELECT DISTINCT(Split.a.value('.', 'VARCHAR(100)')) AS Data  
                             FROM  
                             (
                                 SELECT CAST('<M>' + REPLACE(Text09, ',', '</M><M>') + '</M>' AS XML) AS Data  
                                 FROM  Assets
                                 WHERE Category = 'Service'
                             ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) ORDER BY Data";

    return DatabaseConnection.ExecuteDataTable(sql, 120000);
}

Are you sure it works in SSMS? I mean the `Split.a.value('.', 'VARCHAR(100)')` ?? — Nilesh, Aug 28 '13 at 09:06
Check the XML text, I expect there is a `&` that gets taken for the start of an xml character entity (which should end with a semicolon). Replace that with `&`. — Hans Keﬆing, Aug 28 '13 at 09:07

score 4 · Accepted Answer · answered Aug 28 '13 at 09:11

I think there's some special character in you Text09 column. Try this:

SELECT DISTINCT(Split.a.value('.', 'VARCHAR(100)')) AS Data  
FROM  
(
 SELECT CAST('<M>' + replace((select Text09 + '' for xml path('')), ',', '</M><M>') + '</M>' AS XML) AS Data  
 FROM  Assets
 WHERE Category = 'Service'
) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) ORDER BY Data

see sql fiddle demo

XML parsing: line 1, character 7, semicolon expected

1 Answers1