In this section we will create a user defined function for comma separated string to table using XML in sql server

Earlier we have used while loop and CTE statement to convert comma separated string to table in sql.

You can review the both post at below links.

1. Comma separated string to table  using while loop in SQL
2. Comma separated string to table using CTE in SQL

Here we are going to achieve the same functionality but in different way. We are going to use XML to convert comma separated string to table.  Below is the function which we can use to convert comma separated string to table. Note that in below function it is not compulsory to always use comma. A string can have any type of separator.

CREATE FUNCTION [DBO].[SPLIT]
(
	@STRING NVARCHAR(2000),
	@DELIMITER CHAR(1)
)
RETURNS @TMPTABLE TABLE (COLUMNVALUE NVARCHAR(100))
AS
BEGIN

	DECLARE @xmlData XML
	SELECT @xmlData = CAST('<Node>'+ REPLACE(@STRING,''+@DELIMITER+'','</Node><Node>')+ '</Node>' AS XML)

	INSERT INTO @TMPTABLE
	SELECT d.value('.', 'varchar(200)') AS Val
	FROM @xmlData.nodes('/Node') AS x(d)

	RETURN
END
GO

 

Now to test above function execute below query.

SELECT * FROM DBO.[SPLIT]( ‘ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE,TEN’,’,’)

SELECT * FROM DBO.[SPLIT]( ‘ONE|TWO|THREE|FOUR|FIVE|SIX|SEVEN|EIGHT|NINE|TEN’,’|’)

The output of above query is something like below screen shot.