CREATE FUNCTION dbo.ReplaceTextFromXMLList (@Text VARCHAR(MAX),
@ReplaceXML XML,
@CaseSensitive BIT = 0)
RETURNS VARCHAR(MAX)
/*
DECLARE @ReplaceXML XML = (SELECT 'a' AS [Before], 'b' AS [After]
FOR XML RAW('Replacements'))
SELECT T.item.value('@[Before]', 'varchar(4000)') AS [Before],
T.item.value('@[After]', 'varchar(4000)') AS [After],
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW
FROM @ReplaceXML.nodes('Replacements') AS T (item)
*/
AS
BEGIN
SELECT @Text = CASE WHEN @CaseSensitive = 1
THEN REPLACE(@Text COLLATE SQL_Latin1_General_CP1_CS_AS, [Before] COLLATE SQL_Latin1_General_CP1_CS_AS,
[After] COLLATE SQL_Latin1_General_CP1_CS_AS)
ELSE REPLACE(@Text, [Before], [After])
END
FROM @ReplaceXML.nodes('Replacements') AS T (item)
CROSS APPLY (SELECT T.item.value('@Before', 'varchar(8000)') AS [Before],
T.item.value('@After', 'varchar(8000)') AS [After]) k
RETURN @Text
END