Hi Thomas,

You can try similar logic for splitting the coma delimited value to rows.

---

DECLARE @delimitedstring varchar(8000) = '-ALL- -99,Race 01,Ethnicity 02,Gender 03'

DECLARE @xmlvalue xml

set @xmlvalue = N'<root><r>' + replace(@delimitedstring,',','</r><r>') + '</r></root>'

select

parameterlist.value('.','varchar(100)') as [delimited items]

from @xmlvalue.nodes('//root/r') as t(parameterlist)

----

As you mentioned, you can convert this in to a function which returns the table value:

CREATE FUNCTION splitparameterlist

(

@delimitedstring varchar(8000)

)

returns @parameterlist table

(

parametervalue varchar(100)

)

AS

BEGIN

DECLARE @xmlvalue xml

set @xmlvalue = N'<root><r>' + replace(@delimitedstring,',','</r><r>') + '</r></root>'

insert into @parameterlist(parametervalue)

select

parameterlist.value('.','varchar(100)') as [delimited items]

from @xmlvalue.nodes('//root/r') as t(parameterlist)

RETURN

END

---Sample Select statement--

Select parametervalue from splitparameterlist('-ALL- -99,Race 01,Ethnicity 02,Gender 03')

Reference link: http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx