Moved to FOR XML PATH - How to generate a Delimited String using FOR XML PATH?
Dear Jacob,Nice article,But i am facing problem to run the query what u write here.I got error msg Near XML PATh.Can u tell me what i have to do before run the query.as XML PATH is not working on SSMS windows.thanks
Ajeet,could you post the exact error message you are getting? Also make sure that you are connected to an instance of SQL Server 2005 or above. Run "SELECT @@VERSION" and see what it returns.
I have a rows of color names and sizes which may includes spaces in the name (Soft Pink), when this script runs it makes:row 1: Soft Pinkrow 2: Dark BlueTurn into:row 1: Soft, Pink, Dark, Blueit should return:row 1: Soft Pink, Dark BlueHow can I fix the script to achieve this? Thanks-
Justin,You can achieve it by using the following trick:DECLARE @t TABLE (ColorID INT, Color VARCHAR(50))INSERT INTO @t (ColorID, Color) SELECT 1, 'Soft Pink'INSERT INTO @t (ColorID, Color) SELECT 1, 'Dark Blue'INSERT INTO @t (ColorID, Color) SELECT 2, 'Sky Blue'INSERT INTO @t (ColorID, Color) SELECT 2, 'Ocean Blue'SELECT ColorID, REPLACE(REPLACE((SELECT REPLACE(Color, ' ', '%20') AS 'data()' FROM @t c2 WHERE c2.ColorID = c1.ColorID FOR XML PATH('')), ' ', '|'), '%20', ' ') AS ColorStringFROM @t c1GROUP BY ColorID/*ColorID ColorString----------- --------------------------------------------------1 Soft Pink|Dark Blue2 Sky Blue|Ocean Blue*/
Thanks for your reply, I just recieved it. I did almost the same thing you suggested except I just did instead of thisREPLACE(Color, ' ', '%20') AS 'data()'(Color + '|') AS 'data()'Does the same thing. Thanks again for your help, this is a wonderful script for what I have to do for 100,000's of records.
Oh yeah, then just replace | with ,!