SQL Server批量更新含XML的Config列：提取URL替换Content元素

针对你遇到的问题，核心思路是先将XML节点的文本提取为字符串进行URL截取，再将处理后的结果写回XML节点——因为XML.modify确实不支持直接调用字符串截取函数。以下是两种可行的实操方案：

方案一：CTE+XML查询批量更新（高效推荐） #

适用于数据量较大的场景，通过CTE预处理所有需要更新的行，一次性完成批量替换：

WITH ConfigCTE AS (
    SELECT 
        ID,
        -- 提取Content元素的原始转义文本
        CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)') AS OriginalContent,
        -- 从转义的iframe中截取SharePoint URL
        SUBSTRING(
            CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)'),
            -- 定位src="https://的起始位置，跳过前缀长度
            CHARINDEX('src="https://', CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)')) + 10,
            -- 计算URL的结束位置（下一个"）
            CHARINDEX('"', CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)'), 
                      CHARINDEX('src="https://', CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)')) + 10) 
            - (CHARINDEX('src="https://', CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)')) + 10)
        ) AS SharePointURL
    FROM YourTable
    -- 筛选包含SharePoint链接的目标行
    WHERE CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)') LIKE '%src="https://%.sharepoint.com%'
)
UPDATE t
SET Config = CAST(
    -- 用XML的copy-modify语法替换Content元素内容
    t.ConfigXML.query('
        copy $new := .
        modify replace value of ($new/Root/Content/text())[1] with sql:column("c.SharePointURL")
        return $new
    ') AS nvarchar(max)
)
FROM (SELECT ID, CAST(Config AS XML) AS ConfigXML FROM YourTable) t
JOIN ConfigCTE c ON t.ID = c.ID
WHERE c.SharePointURL IS NOT NULL;

关键说明： #

• 替换/Root/Content为你实际的XML节点路径
• 若URL前缀不是https://，或转义引号用"代替"，需同步调整CHARINDEX的匹配字符串
• 先通过CTE筛选目标行，避免全表扫描

方案二：游标+OPENXML逐行处理（适合小数据量） #

如果数据量较小，用游标逐行处理更直观：

DECLARE @idoc INT;
DECLARE @tempTable TABLE (ID INT, SharePointURL NVARCHAR(MAX));

-- 遍历需要更新的行
DECLARE cur CURSOR FOR
SELECT ID, CAST(Config AS XML) AS ConfigXML
FROM YourTable
WHERE CAST(Config AS XML).value('(/Root/Content)[1]', 'nvarchar(max)') LIKE '%src="https://%.sharepoint.com%';

OPEN cur;
DECLARE @id INT, @xml XML, @originalContent NVARCHAR(MAX);
FETCH NEXT FROM cur INTO @id, @xml;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 初始化XML文档指针
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;

    -- 提取Content元素的转义文本
    SELECT @originalContent = Content
    FROM OPENXML(@idoc, '/Root/Content', 2)
    WITH (Content NVARCHAR(MAX) '.');

    -- 截取SharePoint URL
    INSERT INTO @tempTable (ID, SharePointURL)
    VALUES (
        @id,
        SUBSTRING(
            @originalContent,
            CHARINDEX('src="https://', @originalContent) + 10,
            CHARINDEX('"', @originalContent, CHARINDEX('src="https://', @originalContent) + 10) 
            - (CHARINDEX('src="https://', @originalContent) + 10)
        )
    );

    -- 更新原表的Config列
    UPDATE YourTable
    SET Config = CAST(
        @xml.query('
            copy $new := .
            modify replace value of ($new/Root/Content/text())[1] with sql:column("t.SharePointURL")
            return $new
        ') AS nvarchar(max)
    )
    FROM @tempTable t
    WHERE YourTable.ID = t.ID;

    -- 清理XML文档指针
    EXEC sp_xml_removedocument @idoc;
    FETCH NEXT FROM cur INTO @id, @xml;
END;

CLOSE cur;
DEALLOCATE cur;

测试建议： #

• 执行前先运行CTE或游标里的查询语句，确认SharePointURL提取正确
• 先对小批量数据进行更新测试，验证结果无误后再全量执行

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