Moden's string splitter (or any string splitter) can be used in this manner:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION TVF_2col

(

@col varchar(8000)

)

RETURNS TABLE

AS

RETURN

(

select a.item ky,b.item val from

[dbo].[DelimitedSplit8K](@col,'|') a

inner join

[dbo].[DelimitedSplit8K](@col,'|') b on b.itemnumber = a.itemnumber +1 and a.itemnumber % 2 =1

)

GO

select * from tvf_2col('XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c')