SSRS Hidden Parameter Logic Approach For STRING values

Question

DECLARE @rpmProvider varchar(MAX) = 'Prudhvi, raj,Lalith, Kumar';


CREATE TABLE #PrvFilt(ID varchar(50))
DECLARE @xml xml
BEGIN
   SET @xml = cast(('<X>' + @rpmProvider + '</X>') as xml)
   INSERT INTO #PrvFilt (ID)
   SELECT N.value('.', 'varchar(50)') as value
   FROM @xml.nodes('X') as T(N);
END;

SELECT  * from #PrvFilt
DROP TABLE #PrvFilt

Actual Output :

Prudhvi, raj,Lalith, Kumar

Expected Output :

1. Prudhvi, raj
2. Lalith, Kumar

Answer 1

The problem with your query is that you're not separating the individual names with XML.

Unfortunately, your first and last names are separated by commas and the whole names are also separated by commas.

IF your first and last names will ALWAYS have a comma and a space but the whole names will ONLY BE SEPARATED BY A COMMA, then this should work.

DECLARE @rpmProvider varchar(MAX) = 'Prudhvi, raj,Lalith, Kumar';

DECLARE @xml xml
DECLARE @XMT_TEXT AS VARCHAR(200)

SET @XMT_TEXT = REPLACE(REPLACE(REPLACE(@rpmProvider, ', ', '|'), ',', '</X><X>'), '|', ', ')
SET @xml = cast(('<X>' + @XMT_TEXT + '</X>') as xml)


SELECT N.value('.', 'varchar(50)') as value
FROM @xml.nodes('X') as T(N);