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,'"','&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