SQL Server Split using XML - illegal name character-CodePudding

I am using the following to spilt comma separated string into columns (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);

When I try to split the following:

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

I get the following error: XML parsing: line 1, character 82, illegal name character

Is there a way to include special characters in my function?

CodePudding user response：

Please try the following solution.

Notable points:

CData section protects against XML entities like ampersand and the like.
text() inside .nodes() method is for performance reasons.
TRY_CAST() will return NULL, but will not error out.

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);