Splitting Delimited Strings Using XML in SQL Server

By:   |   Comments (9)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Scripts


This article will help developers looking for a way to split delimited strings in a single query using XML. We generally use a user defined function to do this, which you have probably found in many places that splits the string based on the delimiter passed. But, when it comes to separating the string in a single query without any help of a user defined function there are not many options. I have found a much simpler and shorter way of splitting any string based on a delimiter. I will be using the power of XML to do the splitting of the string instead of a user defined function.


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 and replace the delimiter with some start and end XML tags.

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

Here is what this looks like after the delimiter ',' is replaced by </X><X> tags. When you see the output after converting the string into XML, you will be able to see the string as shown in the image below:


Once the string is converted into XML you can easily query that using XQuery

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

.This will give the output as a separated string as:


Now, say I have a table that has an ID column and comma separated string data as shown below.


I can use the method shown above to split the string.

 cast('<X>'+replace(F.data,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F
 SELECT fdata.D.value('.','varchar(50)') as splitdata 
 FROM f1.xmlfilter.nodes('X') as fdata(D)) O

When the above is run this is the output we get:

split data

This is what is being done. First of all I cast the 'data' column of table @t into an XML data type by replacing the delimiter with starting and ending tags '<X></X>'.

I have used 'CROSS APPLY' for splitting the data. The APPLY clause lets you join a table to a table-valued-function. The APPLY clause acts like a JOIN without the ON clause and comes in two flavors: CROSS and OUTER.

  • The OUTER APPLY clause returns all the rows on the left side (@t) whether they return any rows in the table-valued-function or not. The columns that the table-valued-function returns are null if no rows are returned.
  • The CROSS APPLY only returns rows from the left side (@t) if the table-valued-function returns rows.

This tip hopefully shows you the power of XML and the use of 'CROSS APPLY'. There are other options to split strings in a single query using recursive CTEs, but we will save that for another tip.

Next Steps
  • Now whenever splitting of string is required you can easily cast the string into XML, by replacing the delimiter with XML start and end tags and then use the method shown above to split the string.
  • Take some time to get familiar with the XML features in SQL Server to see if you can simplify your processing.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Divya Agrawal Divya Agrawal's bio is coming soon...

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 8, 2020 - 10:34:38 AM - Charana Back To Top (86433)

If the column has multiple delimiter like "|" , "," how to do cross apply string split using both as delimiter s?

Thank you

Saturday, April 15, 2017 - 1:16:52 AM - VijayRaval Back To Top (54912)


 How can i count in this code?

Monday, December 19, 2016 - 8:51:11 AM - ambi Back To Top (45002)
thanks lot

Wednesday, October 5, 2016 - 5:15:30 AM - Asma Back To Top (43498)

I have the same scenario and I have splitted using xml...but now i want to add the sequence no using row_number()..but the sequence no allocation should be in the same order as in the string.

if I have abc,def,ghi this should be splitted to

1 abc

2 def

3 ghi



Friday, January 22, 2016 - 5:20:29 AM - Eshwar Back To Top (40474)


Thanks to Author 

It's very useful and helpful for other scenario's


Tuesday, November 18, 2014 - 11:11:11 PM - Praveen Rohra Back To Top (35331)

Awesome, scripts runs super fast. Thanks :)

Tuesday, October 2, 2012 - 11:04:11 PM - Rob S Back To Top (19767)

Wow -- this is great!  Like the poster above me, I spent a few hours today looking for a solution as well.  All the solutions were creating a User Defined Function to solve the problem.  Sure that's elegant and all, but my code is running with restricted access so I can't be creating functions in the client's DB.  Therefore this solution is perfect; Thanks!

Thursday, May 24, 2012 - 10:26:30 AM - N.P.L. Back To Top (17640)

Incredible! I've been sitting for 7 straight hours today, looking for a solution just like this one :-D


Thank you!

Tuesday, December 21, 2010 - 1:25:44 PM - Yosi Back To Top (10479)

Hi there


This is a very nice way of doing this split. The problem I ran with though is when the delimiter is / forward slash and there are special characters in the field you are spilliting such as &, ~ etc. Even if you tack utf-8 xml header on it still blows up.



get free sql tips
agree to terms