Use this .. it will help...
DECLARE @str VARCHAR(2000), @strXML VARCHAR(8000),@XML XML
Set @str = 'age=7|Date=15/04/2006^age=5|Date=15/04/2008'
Set @str = '<table><row><col>' + @str + '</col></row></table>'
Set @str = REPLACE(@str,'|','</col><col>')
Set @str = REPLACE(@str,'^','</col></row> <row><col>')
set @str = REPLACE(@str,'Date=','')
Set @strXML = REPLACE(@str,'age=','')
Set @XML = CAST(@strXML AS XML)
SELECT line.col.value('col[1]', 'varchar(1000)') AS Age,
line.col.value('col[2]', 'varchar(1000)') AS Date
FROM @XML.nodes('/table/row') AS line(col)
Output:
Age Date
7 15/04/2006
5 15/04/2008
Hope this helps...