SQL Server存储过程中动态插入XML节点的XQuery问题

SQL Server的XQuery modify()方法不支持直接将变量作为XML元素名使用（比如<{sql:variable("@NODENAME")}>这种写法会触发语法错误），这是因为XQuery的元素构造语法不允许在元素名位置使用变量占位符。针对你遇到的节点不存在场景，以下两种方案可以解决问题：

方案1：通过FOR XML PATH构造动态节点后插入 #

先利用FOR XML PATH动态生成包含目标节点的XML片段，再将其插入到目标XML中，这是最安全且无需动态SQL的方法：

-- 构造要插入的动态节点XML
DECLARE @NewNode XML = (SELECT @NODEVALUE AS [*] FOR XML PATH(@NODENAME), TYPE);

-- 将构造好的节点插入到目标XML的<XML>节点下
SET @XML.modify('insert sql:variable("@NewNode") into (/XML)[1]');

原理说明 #

• FOR XML PATH(@NODENAME)会以@NODENAME的值作为节点名称
• AS [*]指定将@NODEVALUE作为节点的文本内容
• TYPE关键字确保返回结果为XML类型，避免被自动转义

方案2：使用动态SQL（适合复杂场景） #

如果需要直接拼接XQuery语句，可以用动态SQL实现，但要注意处理节点名的注入风险：

DECLARE @ModifyStmt NVARCHAR(MAX);
-- 用QUOTENAME处理节点名，避免SQL注入
SET @ModifyStmt = N'SET @XML.modify(''insert <' + QUOTENAME(@NODENAME, '''') + '>{sql:variable("@NODEVALUE")}</' + QUOTENAME(@NODENAME, '''') + '> into (/XML)[1]'')';

-- 执行动态SQL，传递输出参数@XML
EXEC sp_executesql 
    @ModifyStmt, 
    N'@XML XML OUTPUT, @NODEVALUE NVARCHAR(MAX)', 
    @XML = @XML OUTPUT, 
    @NODEVALUE = @NODEVALUE;

注意事项 #

• 必须使用QUOTENAME包裹节点名，防止特殊字符或注入攻击
• 动态SQL需要通过sp_executesql传递参数，避免硬编码值

完整存储过程示例 #

结合你已实现的节点存在场景，整合后的完整存储过程如下：

CREATE PROCEDURE dbo.UpsertXmlNode
    @XML XML OUTPUT,
    @NODENAME NVARCHAR(100),
    @NODEVALUE NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- 检查目标节点是否存在
    DECLARE @NodeExists BIT = CASE 
        WHEN @XML.exist('/XML/*[local-name()=sql:variable("@NODENAME")]') = 1 THEN 1 
        ELSE 0 
    END;

    IF @NodeExists = 1
    BEGIN
        -- 检查节点是否已有文本内容
        DECLARE @HasText BIT = CASE 
            WHEN @XML.value('count(/XML/*[local-name()=sql:variable("@NODENAME")]/text())', 'BIT') = 1 THEN 1 
            ELSE 0 
        END;

        IF @HasText = 1
        BEGIN
            -- 替换节点现有文本值
            SET @XML.modify('replace value of (/XML/*[local-name()=sql:variable("@NODENAME")]/text())[1] with sql:variable("@NODEVALUE")');
        END
        ELSE
        BEGIN
            -- 向空节点插入文本值
            SET @XML.modify('insert text{sql:variable("@NODEVALUE")} into (/XML/*[local-name()=sql:variable("@NODENAME")])[1]');
        END
    END
    ELSE
    BEGIN
        -- 节点不存在，构造动态节点插入
        DECLARE @NewNode XML = (SELECT @NODEVALUE AS [*] FOR XML PATH(@NODENAME), TYPE);
        SET @XML.modify('insert sql:variable("@NewNode") into (/XML)[1]');
    END
END

测试示例 #

-- 测试插入新节点
DECLARE @TestXML XML = '<XML></XML>';
EXEC dbo.UpsertXmlNode @XML = @TestXML OUTPUT, @NODENAME = 'DISTRICT', @NODEVALUE = '123';
SELECT @TestXML; -- 输出：<XML><DISTRICT>123</DISTRICT></XML>

-- 测试修改现有节点
SET @TestXML = '<XML><DISTRICT>old_value</DISTRICT></XML>';
EXEC dbo.UpsertXmlNode @XML = @TestXML OUTPUT, @NODENAME = 'DISTRICT', @NODEVALUE = '456';
SELECT @TestXML; -- 输出：<XML><DISTRICT>456</DISTRICT></XML>

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