IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[DString] NVARCHAR(255) NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT '0111~Apple' UNION ALL

SELECT '0222~Banana' UNION ALL

SELECT '0333~Carrot' UNION ALL

SELECT '0444~Danish' UNION ALL

SELECT '0555~Eclair'

SELECT

ID

,DString

,dsk1.Item AS Code

,dsk2.Item AS Item

FROM

#TempTable AS tt

CROSS APPLY

dbo.DelimitedSplit8K(tt.DString,'~') AS dsk1

CROSS APPLY

dbo.DelimitedSplit8K(tt.DString,'~') AS dsk2

WHERE

dsk1.ItemNumber = 1

AND dsk2.ItemNumber = 2

OUTPUT

IDDStringCodeItem

10111~Apple0111Apple

20222~Banana0222Banana

30333~Carrot0333Carrot

40444~Danish0444Danish

50555~Eclair0555Eclair

OR AS XML

<?xml version="1.0" ?>

<RESULTS1>

<RECORD>

<ID>1</ID>

<DString>0111~Apple</DString>

<Code>0111</Code>

<Item>Apple</Item>

</RECORD>

<RECORD>

<ID>2</ID>

<DString>0222~Banana</DString>

<Code>0222</Code>

<Item>Banana</Item>

</RECORD>

<RECORD>

<ID>3</ID>

<DString>0333~Carrot</DString>

<Code>0333</Code>

<Item>Carrot</Item>

</RECORD>

<RECORD>

<ID>4</ID>

<DString>0444~Danish</DString>

<Code>0444</Code>

<Item>Danish</Item>

</RECORD>

<RECORD>

<ID>5</ID>

<DString>0555~Eclair</DString>

<Code>0555</Code>

<Item>Eclair</Item>

</RECORD>

</RESULTS1>