declare @JobFamilies nvarchar(max)='aaa|||bbb|||ccc'

CREATE TABLE #JobFamily(

JobFamily nvarchar(50))

DECLARE @idoc int

DECLARE @JobFamiliesXML xml

SET @JobFamiliesXML = CAST('<Root><DataRow><JobFamily>' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@JobFamilies,'"','&quot;'),'&','&amp;'),'<','&lt;'),'>','&gt;'),'|||','</JobFamily></DataRow><DataRow><JobFamily>') + '</JobFamily></DataRow></Root>' AS xml)

EXEC sp_xml_preparedocument @idoc OUTPUT, @JobFamiliesXML

INSERT INTO #JobFamily(JobFamily)

SELECT JobFamily

FROM OPENXML(@idoc,'/Root/DataRow',2)

WITH(JobFamily nvarchar(50))

select * from #JobFamily