SQL Server MVP and my very good friend Jacob Sebastian has written two wonderful articles about SQL Server and XML. I encourage to read this two articles to anybody who are interested in learning SQL and XML. Let us see how to Split a Delimited String.
How to generate a Delimited String is using FOR XML PATH
XQuery Lab 19 – How to parse a delimited string?
I have previously written article about operation of creating a delimited string using COALESCE and no XML but as per telephonic conversation with Jacob COALESCE is only good for one column where as XML can do many more magics.
Jacob has already explained articles in detail so I suggest to read them carefully and digest. If you are not much bigger on XML, you do not have to spend time on learning this XML. Just take the XML script and use it for your need.
If you are wondering what about two articles do, here is a simple explanation. First article explains how to convert Sample 1 to Sample 2 and later one explains how to convert Sample 2 to Sample 1.
XML is very powerful and when combined with SQL Server it always unveils new side of RDBMS and DBMS.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Pinal, problem with for xml path(”) is if the field has xml character like ‘&’, the output will be ‘&’ how do we escape all xml invalid chars?
Thanks in advance.
I found the answer on a linked post and adapted it to make it more readable:
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’
REPLACE(Color, ‘ ‘, ‘%20’) AS ‘data()’
FROM @t c2
WHERE c2.ColorID = c1.ColorID
FOR XML PATH(”), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), ‘ ‘, ‘|’), ‘%20’, ‘ ‘) AS ColorString
FROM @t c1
GROUP BY ColorID