在SQL Server中移除XML标签名中数字的最优方法

现有如下XML示例代码：

declare @pxml xml = 
'<MediaClass>   
    <MediaStream2Client>
        <Title>Test</Title>
        <Type>Book</Type>
        <Price>1.00</Price>
    </MediaStream2Client>
</MediaClass>
'

其中<MediaStream2Client>标签中的数字为1到100的随机数，无法直接解析该标签，请问能否在SQL Server中使用类grep功能移除该标签中的所有数字？

SQL Server没有原生的类grep功能，但可以通过将XML转为字符串处理数字后再转回XML的方式解决，核心是定位标签名里的数字并移除。以下是两种实用方案：

方法一：循环替换标签中的数字 #

先把XML转换成字符串，循环查找并移除标签名里的所有数字：

DECLARE @pxml xml = 
'<MediaClass>   
    <MediaStream2Client>
        <Title>Test</Title>
        <Type>Book</Type>
        <Price>1.00</Price>
    </MediaStream2Client>
</MediaClass>
'
DECLARE @xmlStr NVARCHAR(MAX) = CAST(@pxml AS NVARCHAR(MAX))
DECLARE @pos INT, @digitPos INT

-- 循环处理所有带数字的目标标签
WHILE PATINDEX('%<MediaStream[0-9]%Client>%', @xmlStr) > 0
BEGIN
    SET @pos = PATINDEX('%<MediaStream[0-9]%Client>%', @xmlStr) + LEN('<MediaStream')
    SET @digitPos = PATINDEX('%[0-9]%', SUBSTRING(@xmlStr, @pos, 100))
    -- 移除当前位置的数字，直到该标签段无数字
    WHILE @digitPos > 0
    BEGIN
        SET @xmlStr = STUFF(@xmlStr, @pos + @digitPos - 1, 1, '')
        SET @digitPos = PATINDEX('%[0-9]%', SUBSTRING(@xmlStr, @pos, 100))
    END
END

-- 转回XML类型并查看结果
SET @pxml = CAST(@xmlStr AS xml)
SELECT @pxml

方法二：递归CTE批量移除数字 #

针对1-3位的数字（1到100），用递归CTE可以更高效地批量清除：

DECLARE @pxml xml = 
'<MediaClass>   
    <MediaStream100Client>
        <Title>Test</Title>
        <Type>Book</Type>
        <Price>1.00</Price>
    </MediaStream100Client>
</MediaClass>
'
DECLARE @xmlStr NVARCHAR(MAX) = CAST(@pxml AS NVARCHAR(MAX))

;WITH RemoveDigits AS (
    SELECT @xmlStr AS XmlStr, 1 AS Iteration
    UNION ALL
    SELECT STUFF(XmlStr, PATINDEX('%<MediaStream[0-9]Client>%', XmlStr) + LEN('<MediaStream'), 1, '') AS XmlStr, Iteration + 1
    FROM RemoveDigits
    WHERE PATINDEX('%<MediaStream[0-9]Client>%', XmlStr) > 0
      AND Iteration <= 3 -- 最多处理3位数字（适配100）
)
SELECT @xmlStr = XmlStr
FROM RemoveDigits
WHERE Iteration = (SELECT MAX(Iteration) FROM RemoveDigits)

-- 转回XML并输出
SET @pxml = CAST(@xmlStr AS xml)
SELECT @pxml

说明 #

• 两种方法最终都会把<MediaStreamNClient>（N为1-100的数字）统一转为<MediaStreamClient>，之后就能正常解析这个固定标签。
• 方法一可处理任意位数的数字，方法二针对1-3位数字更高效，可根据实际情况调整递归次数。

内容的提问来源于stack exchange，提问作者neringab