Sowbhari (12/19/2013)
This might help
DECLARE @StartTime VARCHAR(MAX),@X XML
SELECT @StartTime = '7:00 AM
12:30 PM'
SELECT @X = CONVERT(XML,'' + replace(@StartTime,'','</br>') + '</br>')
SELECT x.i.value('.','varchar(max)') AS [StartTime]
FROM @X.nodes('//br') x(i)
This is the correct SQL,somehow the tags are missing in my previous post.
DECLARE @StartTime VARCHAR(MAX),@X XML
SELECT @StartTime = '7:00 AM <br/> 12:30 PM'
SELECT @X = CONVERT(XML,'<br>' + replace(@StartTime,'<br/> ','</br><br>') + '</br>')
SELECT x.i.value('.','varchar(max)') AS [StartTime]
FROM @X.nodes('//br') x(i)