SQL SERVER – XML – Split a Delimited String – Generate a Delimited String

August 17, 2008

SQL SERVER - XML - Split a Delimited String - Generate a Delimited String xmlicon-800x734 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.
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 (https://blog.sqlauthority.com)

Best Practices, MVP, SQL Scripts, SQL Server, SQL Stored Procedure, SQL XML

SQLAuthority News – Tip of the Minute

SQL SERVER – 2005 – Best Practices Analyzer (August 2008)

2 Comments. Leave new

Michael
March 23, 2012 2:35 pm
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.
Reply
- SimonJ
  September 24, 2012 9:33 pm
  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’
  SELECT ColorID,
  REPLACE(REPLACE((SELECT
  REPLACE(Color, ‘ ‘, ‘%20’) AS ‘data()’
  FROM @t c2
  WHERE c2.ColorID = c1.ColorID
  FOR XML PATH(”), TYPE).value(‘.[1]’, ‘NVARCHAR(MAX)’), ‘ ‘, ‘|’), ‘%20’, ‘ ‘) AS ColorString
  FROM @t c1
  GROUP BY ColorID
  Reply