Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Split string using XML

By Divya Agrawal,

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.

Total article views: 10978 | Views in the last 30 days: 16
 
Related Articles
FORUM

Split String

Split comma delimitted String Into Columns

SCRIPT

Split string using multiple delimiters

This script is used to split the string using multiple delimiters

FORUM

Extracting Rows from Delimited Strings

Extracting Rows from Delimited Strings

SCRIPT

Fastest way to split delimited string in SQL Server

Getting rid of R-BAR to split a delimited string. This method is popular for it's simplicity, speed ...

FORUM

SPLITTING A STRING

SPLIT A STRING INTO ROWS AND COLOUMNS

Tags
cross apply    
split string    
string manipulation    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones