sql-server - 如何在 UPDATE 语句中设置变量,然后在 xml.modify() 语句中使用该变量?
问题描述
这是一些演示数据,我想象的解决方案会是什么样子:
CREATE TABLE #lookup_table (
lookup_key VARCHAR(10),
lookup_value VARCHAR(10)
);
INSERT INTO #lookup_table ( lookup_key, lookup_value )
VALUES
( 'key1', 'value1' ),
( 'key2', 'value2' ),
( 'key3', 'value3' ),
( 'key4', 'value4' ),
( 'key5', 'value5' );
CREATE TABLE #dest_table (
lookup_key VARCHAR(10),
xml_data XML
);
INSERT INTO #dest_table (lookup_key, xml_data)
VALUES
('key1', '<root><value /></root>'),
('key2', '<root><value /></root>'),
('key3', '<root><value /></root>'),
('key4', '<root><value /></root>'),
('key5', '<root><value /></root>')
-- Code in question --
DECLARE @value VARCHAR(10);
UPDATE #dest_table
SET @value = LT.lookup_value,
xml_data.modify('insert text{sql:variable("@value")} into (./root/value)[1]')
FROM #dest_table DT
INNER JOIN #lookup_table LT
ON DT.lookup_key = LT.lookup_key;
SELECT * from #dest_table;
根据我的研究,这应该可行,但由于某种原因,@value 始终设置为 NULL 并且节点未更新:
lookup_key xml_data
key1 <root><value /></root>
key2 <root><value /></root>
key3 <root><value /></root>
key4 <root><value /></root>
key5 <root><value /></root>
如果我添加一个 OUTPUT 并使用 SELECT @value 跟踪查询,我们会看到该变量确实已设置——显示了表中的最后一个条目——但 OUTPUT 为 NULL:
DECLARE @value VARCHAR(10);
UPDATE #dest_table
SET @value = LT.lookup_value,
xml_data.modify('insert text{sql:variable("@value")} into (./root/value)[1]')
OUTPUT @value
FROM #dest_table DT
INNER JOIN #lookup_table LT
ON DT.lookup_key = LT.lookup_key;
SELECT @value
SQL Server Management Studio 输出的屏幕截图:
解决方案
@GSerg 提供了一个完美的替代解决方案——消除变量并使用 sql:column("LT.lookup_value") 而不是 sql:variable()。
UPDATE #dest_table
SET xml_data.modify('insert text{sql:column("LT.lookup_value")} into (./root/value)[1]')
FROM #dest_table DT
INNER JOIN #lookup_table LT
ON DT.lookup_key = LT.lookup_key;
假设其他人也是如此,我仍然很想了解为什么问题中提供的解决方案不起作用。