CREATE FUNCTION dbo.ReplaceSchemaFromXMLList (@Text VARCHAR(MAX),
@ReplaceXML XML)
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
DECLARE @Replace TABLE ([Before] VARCHAR(256),
[After] VARCHAR(256))
INSERT @Replace
([Before],
[After])
SELECT DISTINCT
LTRIM(RTRIM(PARSENAME(T.item.value('@Before', 'varchar(8000)'), 1))) AS [Before],
LTRIM(RTRIM(PARSENAME(T.item.value('@After', 'varchar(8000)'), 1))) AS [After]
FROM @ReplaceXML.nodes('Replacements') AS T (item) ;
WITH bc
AS (SELECT AroundChar
FROM ( VALUES ( CHAR(10)), ( CHAR(13)), ( CHAR(32)), ( CHAR(9)), ( '.') ) AS d (AroundChar))
SELECT @Text = REPLACE(@Text + ' ', q.[Before], q.[After])
FROM (SELECT q.[Before],
q.[After]
FROM @Replace k
CROSS JOIN bc AS bef
CROSS JOIN bc AS af
CROSS APPLY (SELECT bef.AroundChar + [Before] + af.AroundChar AS [Before],
bef.AroundChar + [After] + af.AroundChar AS [After]
UNION ALL
SELECT bef.AroundChar + QUOTENAME([Before]) + af.AroundChar AS BEFORE,
bef.AroundChar + QUOTENAME([After]) + af.AroundChar AS AFTER) q
WHERE k.[Before] <> ''
AND k.[Before] <> '') q
RETURN RTRIM(@Text)
END