XML Query is very slow

hi Harshil,

That I understand but i want to know what reason behind using cast and replace?

ok,
Same thing u can acheive by fn_split which is in build in SQL-2008

Declare @String varchar(max) = '1,2,3,4,5,6,7,8,9'
, @Delimiter char(1) = ','
select Value from  dbo.fn_Split(@String,@Delimiter)

And Defination of fn_split

CREATE FUNCTION [dbo].[fn_Split]
(
    @ListValues nvarchar(max),
    @SplitBy nvarchar(5)
) 
RETURNS @ResultValue table
(
    Id int identity(1,1),
    Value nvarchar(100)
)
AS 
BEGIN
    While (Charindex(@SplitBy,@ListValues)>0)
    Begin
          Insert Into @ResultValue (value)
          Select Value = ltrim(rtrim(Substring(@ListValues,1,Charindex(@SplitBy,@ListValues)-1)))
          Set @ListValues = Substring(@ListValues,Charindex(@SplitBy,@ListValues)+len(@SplitBy),len(@ListValues))
    End
    Insert Into @ResultValue (Value)
    Select Value = ltrim(rtrim(@ListValues))   
    Return
END

hope this help.

Hi,

Declare @String varchar(max) = '1,2,3,4,5,6,7,8,9'

, @Delimiter char(1) = ','

SELECT T.split.value('.', 'nvarchar(max)') AS data

FROM (

SELECT  CAST('' + REPLACE(Replace(Replace(@String,'&','&'),'<','<'), @Delimiter, '') + '' AS XML) as T1

) as XMLData

CROSS APPLY T1.nodes('/d') T (split)

above query is spliting data by delimiter.

when string have more than 2000 data(1,2,3,.....,1999,2000) at that time this query get slow.

is there any solution to improve this query.

Thank you.

yes my friend,

but I want what is an actual input and required out put so we can rewrite query in dirrent way to improve performance.

In first glance i can not understand what is actual out required.

Hi,

Can please expain what is your requirement?
Base on you Requirement we can write query.

Declare @String varchar(max) , @Delimiter char(1) set @String ='1;2;3;4;5;6;7;8;9' set @Delimiter = ';' SELECT T.split.value('.', 'nvarchar(max)') AS data FROM ( SELECT CAST('' + REPLACE(@String, @Delimiter, '') + '' AS XML) as T1 ) as XMLData CROSS APPLY T1.nodes('/d') T (split)