Another sql headscratcher

[darkgen]

I have a table as follows:

ColumnA  ColumnB
Fruit    Apple;Pear;Banana

What I need is a mechnism to iterate through the delimited values in ColumnB and generate a new table as follows.

ColumnA  ColumnB
Fruit    Apple
Fruit    Pear
Fruit    Banana

SELECT        a.ColumnA,
split.a.value('.', 'VARCHAR(100)') As ColumnB
from
(select ColumnA,

cast('<M>' + replace(ColumnB, ';', '</M><M>') + '</M>' AS XML) As ColumnB

from Table1
) as a cross apply ColumnB.nodes ('/M') as split(a)

Kinda gets there but balks where ColumnB is empty or does not contain the delimiter ";" (or at least I thinks that's why it balks.).

Appreciate any pointers, TIA Paul!

 

[darkgen]

Hmm; thanks for confirming. Something in the column data perhaps then.... I'll have a further play.

 

[darkgen]

Worked out the issue -
In the data, ColumnB the string values can contain ampersand; for example;

Acid Drops;Rhubarb&Custard;Mints

XML is treating that ampersand as a special character and failing. Is there a means to get it to ignore it as a special character? I Need to retain the string "as is" and not replace characters.

As a workaround I could REPLACE the data, prior and then post, to get around the failure.