Monday, February 1, 2010

Commaseperated value to Row using XML




CREATE FUNCTION [dbo].[SplitString]
(
@delimited nvarchar(max)
) RETURNS @t TABLE
(
ID numeric(18,0) identity(1,1),
Item nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @delimited = replace(@delimited,',','.')
set @xml = N'' + replace(@delimited,'.','') + ''

insert into @t(Item)
select
r.value('.','varchar(5)') as item
from @xml.nodes('//root/r') as records(r)

RETURN

end

Please see image for proper code, here some code missing due to html.
Sorry for the inconvenience.

No comments:

Post a Comment