MSSQL 指定分隔符号 生成数据集

DECLARE
    @xml VARCHAR(MAX)='磨毛：1
缩率：2
干磨：3
湿摩：4
水洗牢度：5
手感：6
防水：7
PH：8
日晒：9'

SET @xml= '<root>'+CHAR(10)+'<r><v>'+REPLACE(REPLACE(@xml,CHAR(10),'</v1></r>'+CHAR(13)+'<r><v>'),'：','</v><v1>')+'</v1></r>'+CHAR(10)+'</root>'
BEGIN TRY

DECLARE
    @xml2 XML= @xml
    
SELECT * FROM(    
SELECT t.v.query('.').value('/r[1]/v[1]','varchar(50)') as gy,
       t.v.query('.').value('/r[1]/v1[1]','varchar(50)') as gyyq
FROM @xml2.nodes('/root/r')t(v)) a
PIVOT(MAX(gyyq) FOR gy IN ([磨毛],[缩率],[干磨],[湿摩],[水洗牢度],[手感],[防水],[PH],[日晒])) AS pvt
END TRY
BEGIN CATCH
    IF @@ERROR>0
        SELECT NULL AS [磨毛],NULL AS [缩率],NULL AS [干磨],NULL AS [湿摩],NULL AS [水洗牢度],NULL AS [手感],NULL AS [防水],NULL AS [PH],NULL AS [日晒]
              ,ERROR_MESSAGE()   
END CATCH

--这里展示的是多列的情况

CREATE FUNCTION dbo.fn_GetSplitData(@Data NVARCHAR(MAX),@SplitChar VARCHAR(50))
RETURNS  @Tb TABLE (
    COL VARCHAR(MAX) DEFAULT ''
  ) 
AS
BEGIN
    DECLARE 
     @Len INT=LEN(@SplitChar)
     
    IF RIGHT(@Data,@Len) <> @SplitChar
        SET @Data=@Data+@SplitChar
        
    ;with roy as 
    (select COl2=cast(left(CASE WHEN @Data='' THEN @SplitChar ELSE @Data END,charindex(@SplitChar,CASE WHEN @Data='' THEN @SplitChar ELSE @Data END+',')-1) as VARCHAR(max))
            ,Split=cast(stuff(CASE WHEN @Data='' THEN @SplitChar ELSE @Data END+@SplitChar,1,charindex(@SplitChar,CASE WHEN @Data='' THEN @SplitChar ELSE @Data END+','),'') as VARCHAR(MAX)) 
    union all
    select COl2=cast(left(Split,charindex(@SplitChar,Split)-1) as VARCHAR(max)),Split= cast(stuff(Split,1,charindex(@SplitChar,Split),'') as VARCHAR(max)) 
    FROM Roy where split>''
    )
    INSERT INTO @Tb(col)
    select COl2 AS Col 
    FROM roy 
    WHERE COl2 <> ''

    RETURN
END
GO