SQL Server使用XQuery动态路径更新XML节点值的方法

你不需要动态拼接XML路径，XPath原生支持*作为任意名称节点的通配符，只需将原有代码中写死的固定根节点名PWR_ViewAll替换为通配符*，即可自动适配AWS_ViewAll、TTWO_ViewAll等任意名称的根节点，原有循环替换的逻辑完全不需要改动。

重构后的可直接运行代码如下（已新增不同根节点的测试场景验证效果）：

DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<PWR_ViewAll>
    <dgvViewAll_Vertical>
        <Section_x0020_>ZB-P1</Section_x0020_>
        <LineItem>B. Riley FBR Inc.</LineItem>
        <Revise_x0020_Date>08-21-2020</Revise_x0020_Date>
        <GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>
    </dgvViewAll_Vertical>
    <dgvViewAll_Vertical>
        <Section_x0020_>CL</Section_x0020_>
        <LineItem>Deutsche Bank</LineItem>
        <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
        <GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>
    </dgvViewAll_Vertical>
    <dgvViewAll_Vertical>
        <Section_x0020_>CL</Section_x0020_>
        <LineItem>Deutsche Bank</LineItem>
        <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
        <GroupKey>Segment Detail~Net Income~RD_100~NBM~~1~CL</GroupKey>
    </dgvViewAll_Vertical>
</PWR_ViewAll>'),
-- 不同根节点测试数据
(N'<AWS_ViewAll>
    <dgvViewAll_Vertical>
        <GroupKey>Test~Total Revenue~AWS</GroupKey>
    </dgvViewAll_Vertical>
</AWS_ViewAll>'),
(N'<TTWO_ViewAll>
    <dgvViewAll_Vertical>
        <GroupKey>Test~Total Revenue~TTWO</GroupKey>
    </dgvViewAll_Vertical>
</TTWO_ViewAll>');
-- DDL和测试数据结束
    
DECLARE @from VARCHAR(30) = '~Total Revenue~'
    , @to VARCHAR(30) = '~Gross Revenue~';
    
-- 替换前查询
SELECT * FROM @tbl
WHERE xmldata.exist('/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;
    
DECLARE @UPDATE_STATUS BIT = 1;
    
WHILE @UPDATE_STATUS > 0
BEGIN
    UPDATE t
    SET xmldata.modify('replace value of (/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]
        with (sql:column("t1.c"))')
    FROM @tbl AS t
        CROSS APPLY (SELECT REPLACE(xmldata.value('(/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]', 'VARCHAR(100)'),@from,@to)) AS t1(c)
    WHERE xmldata.exist('/*/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;
        
    SET @UPDATE_STATUS = @@ROWCOUNT;
    PRINT @UPDATE_STATUS;
END;
    
-- 替换后查询
SELECT * FROM @tbl;

补充说明 #

• 核心改动仅为将所有XPath路径中写死的/PWR_ViewAll/替换为/*/，*是XPath标准通配符，可以匹配任意名称的单个节点，正好对应动态变化的根节点，性能和固定路径写法完全一致。
• 如果后续XML结构可能调整，dgvViewAll_Vertical节点不一定是根节点的直接子级，可以将路径中的/*/替换为//，即路径写为//dgvViewAll_Vertical/GroupKey。//表示匹配任意层级下的对应节点，容错性更高，缺点是查询性能比固定层级路径稍差，可根据实际业务场景选择。

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