Splitting string using xml tags in SQL

Question

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.

Is this TSQL for SQL-Server? Please tag the question appropriately. It makes a difference, as different database engines have different string/XML handling capabilities. — Turophile
– Turophile, Commented May 9, 2014 at 4:52

Mikael Eriksson · Accepted Answer · 2014-05-09 06:29:13Z

1

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>

answered May 9, 2014 at 6:29

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3603308 Over a year ago

Thanks for that. I'll give it a go. How do I then un'XMLify' the xml elements so I can store element one as a string in employee_first column in another table?

Collectives™ on Stack Overflow

Splitting string using xml tags in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related