tsql - SQL Server 2008 - split multi-value column into rows with unique values

Question

In a SQL Server 2008 database, I have a column with multiple values separated by semi-colons. Some values contain colons. Sample data:

key:value;key2:value;blah;foo;bar;A sample value:whee;others
key:value;blah;bar;others
A sample value:whee

I want to get all the unique values from each row in separate rows:

key:value
key2:value
blah
foo
bar
A sample value:whee
others

I've looked at various split functions, but they all seem to deal with hard-coded strings, not strings coming from a column in a table. How can I do this?

Edit: Thomas' answer got it! Here was my final query:

With SampleInputs As
    (
    select distinct myColumn from [myDatabase].[dbo].myTable where myColumn != ''
    )
    , XmlCte As
    (
    Select Cast( '<z>' + Replace( myColumn, ';', '</z><z>' ) + '</z>' As xml ) As XmlValue
    From SampleInputs As I
    )
Select Distinct Y.z.value('.','nvarchar(max)') As Value
From XmlCte
    Cross Apply XmlValue.nodes('//z') Y(z)

I'm guessing the XmlValue.nodes and Y.z.value stuff is magic. O_o

Answer 1

With a split function you use cross apply:

select distinct SS.part
from YourTable
  cross apply dbo.SplitString(YourColumn, ';') as SS

Here the SplitString takes two arguments, the string column and the separator and has a column called part where the values are returned.