Technical Article

Table-Valued Function to Split Strings with Xml

,

Use the table-valued function using a select statement as follows:

select data
from dbo.[StringSplit](''x,c,v,b'','','')
where data = ''x'''

The query above returns one row and one column.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN

Print 'Dropping [StringSplit]'
DROP FUNCTION [dbo].[StringSplit]

END
GO

Print '***********************************************************
Name: StringSplit
Purpose: splits a string using the xml parser.
***********************************************************'

Print 'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
WITH NO WARRANTIES. THIS SOFTWARE IS PROVIDED AS DEMO ONLY. 
YOU CAN MODIFY AND USE AT YOUR OWN RISK.' 
GO

Print '***********************************************************'
Print 'Creating [StringSplit]'
GO

Create FUNCTION [dbo].[StringSplit](@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
 
 DECLARE @textXML XML;
 --replace the delimiter with xml markup. 
 --this converts a string item1,item2,item3 into <elm>item1</elm><elm>item2</elm><elm>item3</elm>
 SELECT @textXML = CAST('<elm>' + REPLACE(@data, @delimiter, '</elm><elm>') + '</elm>' AS XML);

 --select the nodes from the xml into table fields
 INSERT INTO @t(data)
 SELECT T.col.value('.', 'nvarchar(max)') AS data
 FROM @textXML.nodes('/elm') T(col)
 
 RETURN
END
GO

Print 'Usage: Use table function as follows:
select data
from dbo.[StringSplit](''x,c,v,b'','','')
where data = ''x'''

Rate

2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

2 (5)

You rated this post out of 5. Change rating