Split the string using the XML data type in the SQL server

Introduction

In this article we going to discuss about how to split the string and convert in to the XML format and from that insert in to on temp table

Create one sample input variable

DECLARE @sampleText varchar(500)='lokesh1,lokesh2,lokesh3,lokesh4,lokesh5,lokesh6,lokesh7,lokesh8,lokesh9,lokesh10'

And create one sample variable with XML data type like  

In this XML data type I assumed <lok></lok> as  the tag notation

Declare @XML XML

set @XML=CAST('<lok>'+REPLACE(@sampleText,',','</lok><lok>')+'</lok>' as XML)

And create one temp table like

DECLARE @temp AS TABLE 

( 

      rownum INT IDENTITY(1, 1), 

      name   VARCHAR(50) 

)

And itrate through the XML data and insert in to the temple table

insert into @temp(name) select T.i.value('.', 'varchar(max)')

from @XML.nodes('lok') T(i)

Complete code

DECLARE @sampleText varchar(500)='lokesh1,lokesh2,lokesh3,lokesh4,lokesh5,lokesh6,lokesh7,lokesh8,lokesh9,lokesh10'

Declare @XML XML

set @XML=CAST('<lok>'+REPLACE(@sampleText,',','</lok><lok>')+'</lok>' as XML)

DECLARE @temp AS TABLE 

( 

      rownum INT IDENTITY(1, 1), 

      name   VARCHAR(50) 

) 

select @XML

insert into @temp(name) select T.i.value('.', 'varchar(max)')

from @XML.nodes('lok') T(i)

SELECT * from @temp

BY using single select statement 

DECLARE @TMP_TABLE AS TABLE (DATAS VARCHAR(250))

INSERT INTO @TMP_TABLE

SELECT 'lokesh1,lokesh2,lokesh3,lokesh4,lokesh5,lokesh6,lokesh7,lokesh8,lokesh9,lokesh10'

SELECT TT.DATAS,Split.a.value('.', 'VARCHAR(100)') AS String FROM (

SELECT DATAS,CAST('<lok>'+REPLACE(DATAS,',','</lok><lok>')+'</lok>' as XML)AS STRINGS FROM @TMP_TABLE

) AS TT CROSS APPLY STRINGS.nodes ('/lok') AS Split(a);