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 :

Prudhvi, raj

Lalith, Kumar

The question was not clear. On what criteria you are expecting the results like this. — Roshan Nuvvula, Feb 12 '19 at 10:28
Looks like you need to get the string split by commas and join them. I'd say you could use [this answer](https://stackoverflow.com/a/15108499/10755978) and adapt it to do what you need to do. — Steve-o169, Feb 12 '19 at 13:54

Hannover Fist · Accepted Answer · 2019-02-12T19:32:41.110

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);

SSRS Hidden Parameter Logic Approach For STRING values

1 Answers1