符号分割的字符串转换为XML

把某一符串分割的字符串转换为 XML格式:

 

DECLARE @str NVARCHAR(MAX) = N'fd,re,45,tyu,976,qwer,gdsg,uyt'
DECLARE @xml XML = CAST(N'<insus>' + REPLACE(@str, N',',N'</insus><insus>')  + N'</insus>' AS XML)
SELECT @xml
Source Code

 

可以把它写成一个自定义函数:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-26
-- Update date: 2019-05-26
-- Description: 转换为XML
CREATE FUNCTION [dbo].[svf_ConvertToXML] ( 
     @comma_delimited_string NVARCHAR(MAX)
)
RETURNS XML
AS
BEGIN     
    RETURN CAST(N'<insus>' + REPLACE(@comma_delimited_string, N',',N'</insus><insus>')  + N'</insus>' AS XML)
END
Source Code

 

 

posted @ 2019-05-26 22:25  Insus.NET  阅读(774)  评论(0编辑  收藏  举报