SQL Server中如何将含多别名的XML批量插入数据表

嘿，我来帮你搞定这个问题！你的核心需求是把每个<Entity>下的所有别名和对应的名称关联起来，插入到表中生成4条独立记录对吧？你的原代码只拿到每个Entity的第一个别名，问题出在XPath路径不对，而且没正确关联Entity和它的下属别名节点。

下面给你两种可行的解决方案，第二种更推荐哦：

方案一：修正OPENXML的路径关联 #

首先，你需要确保遍历的是每个<Entity>下的所有<aliases>/<alias>节点，而不是直接从根节点找别名。这里分两种写法：

写法1：嵌套OPENXML关联层级 #

先声明你的XML变量，然后用sp_xml_preparedocument初始化，再通过嵌套OPENXML先取Entity，再取每个Entity下的别名：

DECLARE @ixml INT
DECLARE @xml XML = N'
<Entity>
  <name>John</name>
  <aliases><alias>Johnny</alias></aliases>
  <aliases><alias>Johnson</alias></aliases>
</Entity>
<Entity>
  <name>Smith</name>
  <aliases><alias>Smithy</alias></aliases>
  <aliases><alias>Schmit</alias></aliases>
</Entity>'

EXEC sp_xml_preparedocument @ixml OUTPUT, @xml

INSERT INTO TESTTABLE (name, alias)
SELECT 
    e.name,
    a.alias
FROM OPENXML(@ixml, '/Entity', 2)
WITH (
    name VARCHAR(255) 'name',
    aliases XML 'aliases' -- 把当前Entity下的所有aliases节点转为XML类型
) e
CROSS APPLY OPENXML(CAST(e.aliases AS INT), '/aliases/alias', 2)
WITH (
    alias VARCHAR(255) '.'
) a

EXEC sp_xml_removedocument @ixml -- 别忘了清理XML文档

写法2：用相对XPath直接关联 #

更简洁的方式，直接从Entity下的alias节点入手，通过相对路径找到对应的name：

DECLARE @ixml INT
DECLARE @xml XML = N'
<Entity>
  <name>John</name>
  <aliases><alias>Johnny</alias></aliases>
  <aliases><alias>Johnson</alias></aliases>
</Entity>
<Entity>
  <name>Smith</name>
  <aliases><alias>Smithy</alias></aliases>
  <aliases><alias>Schmit</alias></aliases>
</Entity>'

EXEC sp_xml_preparedocument @ixml OUTPUT, @xml

INSERT INTO TESTTABLE (name, alias)
SELECT 
    name,
    alias
FROM OPENXML(@ixml, '/Entity/aliases/alias', 2)
WITH (
    name VARCHAR(255) '../../name', -- 向上两级定位到Entity的name节点
    alias VARCHAR(255) '.'
)

EXEC sp_xml_removedocument @ixml

方案二：使用SQL Server原生XML方法（推荐） #

如果你的SQL Server版本是2005及以上，强烈推荐用原生XML的nodes()方法，不需要依赖sp_xml_preparedocument，代码更直观，性能也更好：

DECLARE @xml XML = N'
<Entity>
  <name>John</name>
  <aliases><alias>Johnny</alias></aliases>
  <aliases><alias>Johnson</alias></aliases>
</Entity>
<Entity>
  <name>Smith</name>
  <aliases><alias>Smithy</alias></aliases>
  <aliases><alias>Schmit</alias></aliases>
</Entity>'

INSERT INTO TESTTABLE (name, alias)
SELECT 
    -- 从当前Entity节点中提取name值
    e.value('(name)[1]', 'VARCHAR(255)') AS name,
    -- 从当前alias节点中提取值
    a.value('.', 'VARCHAR(255)') AS alias
FROM @xml.nodes('/Entity') AS Entities(e)
-- 交叉应用，把每个Entity下的所有alias节点拆分成独立行
CROSS APPLY e.nodes('aliases/alias') AS Aliases(a)

执行完上面的代码，你就能得到想要的4条记录：

name | alias
John | Johnny
John | Johnson
Smith| Smithy
Smith| Schmit

为什么你的原代码失效？ #

你的原代码用了/Aliases作为XPath路径，问题有三个：

1. XML是区分大小写的！你的XML里是小写的<aliases>，但你写的是大写的Aliases，路径不匹配；
2. 路径层级错误：<aliases>是嵌套在<Entity>内部的，直接从根节点找/Aliases根本找不到正确的节点，自然只能拿到空或者错误的结果；
3. 没有关联对应的name，导致无法把别名和所属的Entity绑定。

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