SQL Server如何通过XQuery更新xml中的值，当某些部分是动态的

Question

我必须替换所有的文本~Total Revenue~with~Gross Revenue~

查看下面的代码，它工作得很好。

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>');
-- DDL and sample data population, end

DECLARE @from VARCHAR(30) = '~Total Revenue~'
, @to VARCHAR(30) = '~Gross Revenue~';

-- before
SELECT * FROM @tbl
WHERE xmldata.exist('/PWR_ViewAll/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 (/PWR_ViewAll/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('(/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]/text())[1]', 'VARCHAR(100)'),@from,@to)) AS t1(c)
WHERE xmldata.exist('/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[contains(text()[1], sql:variable("@from"))]') = 1;

SET @UPDATE_STATUS = @@ROWCOUNT;
PRINT @UPDATE_STATUS;
END;

-- after
SELECT * FROM @tbl;

问题是这个元素不是固定的/pwr_viewwall/因为它可以是

/aws_viewwall/OR/ttwo_viewwall/等

所以如何重构上面的代码作为结果，我可以使这部分/pwr_viewwall/动态的。请引导我。由于

Answer 1

我找到了出路，现在附加的代码是工作的。

DECLARE @Ticker_ViewAll VARCHAR(MAX)='/PWR_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>');
-- DDL and sample data population, end

DECLARE @from VARCHAR(30) = '~Total Revenue~'
, @to VARCHAR(30) = '~Gross Revenue~';

-- before
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;

-- after
SELECT * FROM @tbl;