Split String using XML

This blog will help developers looking to split strings in a single query using XML. We generally use a user defined function, which we have used in our database that splits the string based on the delimiter passed. But, when it comes to separate the string in a single query without any help of user defined function we often get panic. I have found a much optimized and shorter way of splitting any string based on the delimiter passed. I will be using the power of XML to do the same.

Let's say for example there is a string 'A,B,C,D,E' and I want to split it based on the delimiter ','. The first step would be to convert that string into XML, replacing the delimiter with some start and end XML tag.

Declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)

SET @str='A,B,C,D,E'

SET @delimiter =','

SET @xml = cast((''+replace(@str,@delimiter ,'')+'') as xml)

SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

--OUTPUT

--A

--B

--C

--D

--E

It will improve your performance.

Enjoy!!!!!!