SQL Server 2005: How to perform a split on a string

Question

I have the following string that I need to split from a field called symbols

234|23|HC

This is my current SQL statement

declare @t xml;
Set @t = (
Select symbols from tc for xml auto, elements)

Select @t;

which produces <symbols>234|23|HC</symbols>

but I need to split the string into child nodes so the result is like this:

<symbols>
       <symbol>234</symbol>
       <symbol>23</symbol>
       <symbol>HC</symbol>
</symbols>  

Answer 1

A replace version that takes care of the problem characters.

declare @T table(symbol varchar(50))
insert into @T values ('234|23|HC|Some problem chars <> &')

select cast('<symbols><symbol>'+
             replace(cast(cast('' as xml).query('sql:column("symbol")') as varchar(max)), 
                     '|', 
                     '</symbol><symbol>')+
             '</symbol></symbols> ' as xml)
from @T

Result:

<symbols>
  <symbol>234</symbol>
  <symbol>23</symbol>
  <symbol>HC</symbol>
  <symbol>Some problem chars &lt;&gt; &amp;</symbol>
</symbols>