xml 分割字符串
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,'"','"'),'&','&'),'<','<'),'>','>'),'|||','</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