CODEWITHSUNDEEP
sql-serverxml
user3603308
user3603308

Reputation: 355

Splitting string using xml tags in SQL

I want to split a string by replacing the spaces with xml tags in sql. I have a user defined function as follows:

CREATE FUNCTION [dbo].[stringtoxml]
  (
   @string_to_split NVARCHAR(MAX),
   @delimiter NVARCHAR(100)
   )
   RETURNS XML
   AS
   BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@string_to_split, @delimiter,'</t><t>') + '</t>'
    RETURN @xml
   END

I'm storing the xml in a field employee_name with a datatype of xml. In another table, I want to be able to read the first element as the first name, second element as the middle initial if the format is one alpha character followed by a full stop and the third element as the last name.

There are invalid characters in the @string_to_split - for example 'John O'Smith'.

Is there a way to get around the invalid characters without having to do:

DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(REPLACE(REPLACE(@string_to_split,'''','&apos;'),'&','&amp;'), @delimiter,'</t><t>') + '</t>'
RETURN @xml

and then to do another replace to remove &apos and &amp when I'm reading the individual elements in the other table.

Upvotes: 1

Views: 2821

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can "XMLify" your string using for xml path.

Query:

declare @string_to_split nvarchar(max) = '&,<,>,'',"'
declare @delimiter nvarchar(100) = ','
declare @xml xml

set @xml = N'<t>' + replace((select @string_to_split for xml path('')), @delimiter, N'</t><t>') + N'</t>'

select @xml

Result:

<t>&amp;</t>
<t>&lt;</t>
<t>&gt;</t>
<t>'</t>
<t>"</t>

Upvotes: 1

Related Questions