如何用TSQL的modify方法初始化NULL XML列并填充嵌套元素？

我发现似乎无法直接用modify为NULL列创建根XML节点。

示例表 #

Id  Val  MyXML
1   2    NULL
2   5    NULL

预期结果 #

1   2    <data><val>2</val></data>
2   5    <data><val>5</val></data>

我目前想到两种方法：

• 方法一：字符串拼接

UPDATE MyTable
SET MyXML = '<data><val>' + CONVERT(VARCHAR(10),Val) + '</val></data>'

• 方法二：分两次执行查询，先创建根节点再添加元素

UPDATE MyTable
SET MyXML = '<data></data>'
UPDATE MyTable
SET MyXML.modify('insert <val>{sql:column("Val")}</val> into /data[1]')

我想知道下面这种理想写法是否可行：

UPDATE MyTable
SET MyXML.modify('insert <data><val>{sql:column("Val")}</val></data>')

你的理想写法不可行，因为modify方法只能作用于已存在的XML实例——当MyXML列值为NULL时，没有可操作的XML对象，直接调用modify会报错。

下面分析你提到的两种方法，并给出更优方案：

1. 字符串拼接法：实现简单，但存在明显缺陷——如果Val包含<、&这类XML特殊字符，直接拼接会生成格式错误的XML，甚至可能引发注入风险。
2. 分两次更新法：能保证XML格式合法，但需要两次遍历整张表，数据量较大时效率偏低。

更优方案：用FOR XML PATH直接生成合法XML #

这种方法既不用多次更新，又能确保XML格式完全合规，是更推荐的写法：

UPDATE MyTable
SET MyXML = (
    SELECT Val AS val
    FOR XML PATH(''), ROOT('data'), TYPE
)
WHERE MyXML IS NULL

• FOR XML PATH('')会自动把Val的值包装成<val>值</val>格式
• ROOT('data')为结果添加指定的根节点<data>
• TYPE关键字指定返回原生XML类型，直接赋值给XML列无需额外转换

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