SQL Server Split using XML - illegal name character

Question

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?

You need to escape the `&`, as well as other XML characters. For other split options, see https://stackoverflow.com/questions/10914576/t-sql-split-string — Charlieface, Jul 03 '22 at 16:38
I suggest looking at a Tally Splitter or using CLR. rCTE splitters and `WHILE` loop splitters exist, however, these tend be be the slowest of the bunch. — Thom A, Jul 03 '22 at 17:22

Yitzhak Khabinsky · Accepted Answer · 2022-07-03T17:47:07.600

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

SQL Server Split using XML - illegal name character

1 Answers1