SQL Server分割使用XML -非法的名称字符

Question

我使用以下命令将逗号分隔的字符串拆分为列(SQL Server 2014):

function [dbo].[splitString](@input Varchar(max), @Splitter Varchar(99)) returns table as
Return
SELECT Split.a.value('.', 'NVARCHAR(max)') AS Data FROM
( SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data 
) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

当我尝试分割以下内容时:

Front Office,Food & Beverage,Housekeeping,Human Resource & Training,Reservation,Other

我得到以下错误:XML解析:第1行，字符82，非法名称字符

是否有一种方法可以在我的函数中包含特殊字符?

Answer 1

请尝试以下解决方案。

值得注意的点:

• CData节保护XML实体，如&和喜欢。
• text()inside.nodes()方法是出于性能考虑。
• TRY_CAST()将返回NULL，但不会出错。

SQL

DECLARE @input Varchar(max) = 'Front Office,Food & Beverage,Housekeeping,Human Resource & Training,Reservation,Other'
, @Splitter Varchar(99) = ',';
SELECT Split.a.value('.', 'NVARCHAR(max)') AS Data 
FROM ( SELECT TRY_CAST('<M><![CDATA[' + REPLACE(@input, @Splitter, ']]></M><M><![CDATA[') + ']]></M>' AS XML) AS Data 
) AS A CROSS APPLY Data.nodes ('/M/text()') AS Split(a);