如何使用XQuery替换SQL中XML类型数据的整个父元素

你之前踩的坑我太懂了——replace value of确实不是干这个活的！它的设计目标只是替换属性值或者只有纯文本内容的元素，而你要替换的是带有子元素的整个<Row>节点，属于复杂内容，所以肯定会报错。

其实用.modify()完全可以解决，只是得换个思路：先删掉原来的第一个<Row>，再把新的<Row>插入到<Table>的最前面。这是SQL Server里处理复杂XML节点替换的标准操作，而且效率很高。

给你看完整的可运行代码，刚好匹配你的需求：

DECLARE @x xml = '<Table> <Row> <Column>CustomerNo</Column> <Column>Name</Column> </Row> <Row> <Column>1</Column> <Column>Bob</Column> </Row> <Row> <Column>2</Column> <Column>Jill</Column> </Row> </Table>'
DECLARE @replace xml = '<Table> <Row> <Column>Customer No</Column> <Column>Name</Column> </Row> </Table>'

-- 第一步：删除原XML中的第一个<Row>节点
SET @x.modify('delete (/Table/Row)[1]')

-- 第二步：把@replace里的新<Row>插入到<Table>的最前面
SET @x.modify('insert sql:variable("@replace").query("/Table/Row") as first into (/Table)[1]')

-- 验证最终结果
SELECT @x

为什么这个方法可行？ #

1. delete操作：(/Table/Row)[1]精准定位到你要替换的第一个行节点，直接移除它；
2. insert操作：sql:variable("@replace").query("/Table/Row")从你的替换XML里提取出<Row>节点，as first into (/Table)[1]确保它被插入到<Table>的第一个子节点位置，刚好补上原来删除的位置。

这里要注意：SQL Server的xml.modify()每次只能执行一个XQuery DML操作，所以必须分两次调用。不过这完全不是问题，两次操作都是轻量的DOM操作，不会有性能瓶颈。

如果你不想用@replace变量，也可以直接把新的<Row>写在insert语句里，效果一样：

SET @x.modify('insert <Row> <Column>Customer No</Column> <Column>Name</Column> </Row> as first into (/Table)[1]')

运行完之后，你就能得到想要的最终XML结构啦。

内容来源于stack exchange