Journey to SQL Authority with Pinal Dave

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.

How to generate a Delimited String using FOR XML PATH

XQuery Lab 19 - How to parse a delimited string?

I have previously written article about operation of creating 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 big 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 article do, here is 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.

Sample 1: 
/*
CompanyID   CompanyCode
----------- -----------
1           1
1           2
2           1
2           2
2           3
2           4
3           1
3           2
*/

Sample 2:
/*
CompanyID   CompanyString
----------- -------------------------
1           1|2
2           1|2|3|4
3           1|2
*/

XML is very powerful and when combined with SQL Server it always unveils new side of RDBMS and DBMS.

Reference : Pinal Dave (http://www.SQLAuthority.com)