XML分析:第1行，字符7，应为分号

本文关键字：字符 1行 XML 分析 | 更新日期: 2023-09-27 18:29:13

在C#中运行此XML SQL时发生错误。缺少什么？它在SQL Server管理工作室中运行良好，但在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);
}

XML分析:第1行，字符7，应为分号

我认为Text09列中有一些特殊字符。试试这个：

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

请参阅sql-fiddle演示

相关文章：