SQL Server导入XML数据时多键关联失效问题求助

看起来你遇到的问题是：每个<person>下有多个<key>，但用OPENXML查询时，所有<name>节点都只关联了第一个<key>值。这是因为OPENXML在引用../../keys/key这种路径时，默认只会返回匹配到的第一个节点值，不会自动遍历所有<key>节点。下面给你两种解决方案，优先推荐更现代的XQuery方法：

方案1：用XQuery替代OPENXML（更简洁可靠） #

SQL Server支持的XQuery比传统的OPENXML更灵活，处理多节点关联场景更得心应手。直接看代码：

DECLARE @x xml;
SELECT @x = P FROM OPENROWSET (BULK 'C:\person.xml', SINGLE_BLOB) AS Person(P);

-- 生成每个name和所有key的关联记录
SELECT
    KeyValue = key_node.value('.', 'nvarchar(100)'),
    Prename = name_node.value('prename[1]', 'varchar(100)'),
    Surname = name_node.value('surname[1]', 'varchar(100)'),
    Sex = @x.value('(/person/sex)[1]', 'varchar(50)')
FROM @x.nodes('/person/keys/key') AS keys_tbl(key_node)
CROSS APPLY @x.nodes('/person/names/name') AS names_tbl(name_node);

原理说明 #

• nodes()方法会把指定路径下的所有节点拆分成行集，这里先拆分出所有<key>节点，再拆分出所有<name>节点。
• CROSS APPLY会把两个行集做笛卡尔积，这样每个<name>就会和每个<key>一一对应，完美解决关联问题。
• 不需要手动管理XML文档句柄，避免了sp_xml_preparedocument可能带来的内存泄漏问题。

方案2：改进OPENXML的写法（如果必须用OPENXML） #

如果你因为某些原因要继续用OPENXML，可以先把所有<key>提取到临时表，再和<name>的结果做交叉关联：

DECLARE @x xml; 
DECLARE @hdoc int; 
SELECT @x = P FROM OPENROWSET (BULK 'C:\person.xml', SINGLE_BLOB) AS Person(P);
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x;

-- 先把所有key存入临时表
DECLARE @temp_keys TABLE (key_value nvarchar(100));
INSERT INTO @temp_keys
SELECT * FROM OPENXML (@hdoc, '/person/keys/key', 2)
WITH (key_value nvarchar(100) '.');

-- 提取name数据，再和临时表的key做交叉关联
SELECT
    tk.key_value,
    p.prename,
    p.surname,
    p.sex
FROM OPENXML (@hdoc, '/person/names/name', 2)
WITH (
    prename varchar(100),
    surname varchar(100),
    sex varchar(50) '../../sex'
) AS p
CROSS JOIN @temp_keys tk;

-- 记得清理文档句柄
EXEC sp_xml_removedocument @hdoc;

为什么原代码只取第一个key？ #

原代码里的../../keys/key路径，OPENXML只会返回该路径下第一个匹配的节点值，不会遍历所有<key>。所以必须先单独提取所有key，再通过交叉关联让每个name对应所有key。

额外提示 #

如果你的XML里有多个<person>节点，只需要调整XPath路径为/persons/person，然后用嵌套的CROSS APPLY处理每个person下的keys和names即可，比如：

SELECT
    PersonID = person_node.value('(keys/key)[1]', 'nvarchar(100)'), -- 可选，用第一个key做person标识
    KeyValue = key_node.value('.', 'nvarchar(100)'),
    Prename = name_node.value('prename[1]', 'varchar(100)'),
    Surname = name_node.value('surname[1]', 'varchar(100)'),
    Sex = person_node.value('sex[1]', 'varchar(50)')
FROM @x.nodes('/persons/person') AS persons_tbl(person_node)
CROSS APPLY person_node.nodes('keys/key') AS keys_tbl(key_node)
CROSS APPLY person_node.nodes('names/name') AS names_tbl(name_node);

这样就能处理多person的场景啦。

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