Harshil Shah

Harshil Shah

  • NA
  • 9
  • 15.2k

XML Query is very slow

Mar 28 2012 8:28 AM
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('<d>' + REPLACE(Replace(Replace(@String,'&','&amp;'),'<','&lt;'), @Delimiter, '</d><d>') + '</d>' AS XML) as T1
) as XMLData
CROSS APPLY T1.nodes('/d') T (split)

here @String have 9 data, when @String have more than 2000 data at that time Query get very slow . is there any ohter solution to improve this query.

Thank you.

Answers (6)