This article would help developers looking to split strings in a single query using XML. We generally use a user defined function, which you all may have found at many places 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(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)

Here as shown above, the delimiter ',' is replaced by </X><X> tags. When you will 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.

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 if I have a table as having an ID column and comma separated string as data column.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(50))
INSERT INTO @t(data) SELECT 'AA,AB,AC,AD'
INSERT INTO @t(data) SELECT 'BA,BB,BC'

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

select F1.id,
 F1.data,
 O.splitdata 
 from
 (
 select *,
 cast('<X>'+replace(F.data,',','</X><X>')+'</X>' as XML) as xmlfilter
 from @t F
 )F1
 cross apply
 ( 
 select fdata.D.value('.','varchar(50)') as splitdata 
 from f1.xmlfilter.nodes('X') as fdata(D)) O

First of all, cast the 'data' column of table @t into XML data type by replacing the delimiter by starting and ending tags '<X></X>'.

I have used 'CROSS APPLY' for splitting the data. APPLY clause let's you join a table to a table-valued-function. The APPLY clause acts like a JOIN without the ON clause 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.

Executing the select statement mentioned above would display the following output:

This article might have made you clear of the power of XML and a very good use of 'CROSS APPLY'. There are other options to split strings in a single query using recursive CTEs.

Now whenever splitting of string is required you can easily cast the string into XML, by replacing the delimiter by XML start and end tags and then use the method shown above to split the string.