SQL Server解析XML如何获取父节点ROW的NU_LINE字段值

你当前代码的OPENXML查询上下文定位到了/STRUCTURE/GRILLE/ROWS/ROW/CHAMPS/CHAMP节点，而NU_LINE是CHAMP节点向上两层的ROW节点的子元素，默认无法直接读取到该值，所以返回NULL。

只需修改OPENXML的WITH子句中NU_LINE的映射规则，通过相对路径向上查找父级ROW节点的NU_LINE值即可：

INSERT @Champs   
    SELECT *   
    FROM OPENXML(@docHandle, N'/STRUCTURE/GRILLE/ROWS/ROW/CHAMPS/CHAMP') 
    WITH (
        NU_LINE VARCHAR(4) '../../NU_LINE', -- 向上两层到ROW节点后读取NU_LINE
        "KEY" VARCHAR(200), 
        LABEL VARCHAR(200), 
        API_NAME VARCHAR(200), 
        API_FIELD VARCHAR(200), 
        "VALUE" VARCHAR(MAX)
    )

注意：使用sp_xml_preparedocument后必须调用sp_xml_removedocument @docHandle释放内存，避免长期运行出现内存泄漏。

SQL Server从2005版本开始支持原生XML节点处理方法，性能和稳定性都优于遗留的OPENXML接口，不需要手动管理内存，实现代码如下：

DECLARE @xml XML = CAST(@xmlDocument AS XML)

INSERT @Champs (NU_LINE, [KEY], LABEL, API_NAME, API_FIELD, [VALUE])
SELECT
    R.RowNode.value('(NU_LINE/text())[1]', 'VARCHAR(4)') AS NU_LINE,
    C.ChampNode.value('@KEY', 'VARCHAR(200)') AS [KEY],
    C.ChampNode.value('@LABEL', 'VARCHAR(200)') AS LABEL,
    C.ChampNode.value('@API_NAME', 'VARCHAR(200)') AS API_NAME,
    C.ChampNode.value('@API_FIELD', 'VARCHAR(200)') AS API_FIELD,
    C.ChampNode.value('@VALUE', 'VARCHAR(MAX)') AS [VALUE]
FROM @xml.nodes('/STRUCTURE/GRILLE/ROWS/ROW') AS R(RowNode)
CROSS APPLY R.RowNode.nodes('CHAMPS/CHAMP') AS C(ChampNode)

内容的提问来源于stack exchange，提问作者eric.bryan