Most succinct way to transform a CSV string to a table in T-SQL?

Question

-- Given a CSV string like this:

declare @roles varchar(800)
select  @roles = 'Pub,RegUser,ServiceAdmin'

-- Question: How to get roles into a table view like this:

select  'Pub'
union
select  'RegUser'
union
select  'ServiceAdmin'

---

After posting this, I started playing with some dynamic SQL. This seems to work, but seems like there might be some security risks by using dynamic SQL - thoughts on this?

declare @rolesSql varchar(800)
select  @rolesSql = 'select ''' + replace(@roles, ',', ''' union select ''') + ''''
exec(@rolesSql)

Answer 1

Using SQL Server's built in XML parsing is also an option. Of course, this glosses over all the nuances of an RFC-4180 compliant CSV.

-- Given a CSV string like this:
declare @roles varchar(800)
select  @roles = 'Pub,RegUser,ServiceAdmin'

-- Here's the XML way
select split.csv.value('.', 'varchar(100)') as value
from (
     select cast('<x>' + replace(@roles, ',', '</x><x>') + '</x>' as xml) as data
) as csv
cross apply data.nodes('/x') as split(csv)

If you are using SQL 2016+, using string_split is better, but this is a common way to do this prior to SQL 2016.