SQL解析XML生成指定结果集时N列取值错误问题求助

问题根源是XQuery上下文取值错位：常规写法如果先全局提取所有<IROW>的<N>值，再全局提取所有<FIELD>节点，会导致所有FIELD都绑定第一个<N>的取值，无法和所属的<IROW>节点对应。

测试用XML示例（可直接复现场景） #

<ROOT>
    <IROW>
        <N>1</N>
        <COL_ACTION>UPDATE</COL_ACTION>
        <FIELDS>
            <FIELD>
                <COL_NAME_I>USER_ID</COL_NAME_I>
                <COL_VALUE_AFTER>1001</COL_VALUE_AFTER>
            </FIELD>
            <FIELD>
                <COL_NAME_I>USER_NAME</COL_NAME_I>
                <COL_VALUE_AFTER>张三</COL_VALUE_AFTER>
            </FIELD>
        </FIELDS>
    </IROW>
    <IROW>
        <N>2</N>
        <COL_ACTION>INSERT</COL_ACTION>
        <FIELDS>
            <FIELD>
                <COL_NAME_I>USER_ID</COL_NAME_I>
                <COL_VALUE_AFTER>1002</COL_VALUE_AFTER>
            </FIELD>
            <FIELD>
                <COL_NAME_I>USER_NAME</COL_NAME_I>
                <COL_VALUE_AFTER>李四</COL_VALUE_AFTER>
            </FIELD>
        </FIELDS>
    </IROW>
</ROOT>

正确实现代码（SQL Server 版本） #

DECLARE @xml XML = '上述示例XML内容'

SELECT
    T.IROW.value('(COL_ACTION/text())[1]', 'VARCHAR(20)') AS COL_ACTION,
    T.IROW.value('(N/text())[1]', 'INT') AS N,
    F.FIELD.value('(COL_NAME_I/text())[1]', 'VARCHAR(50)') AS COL_NAME_I,
    F.FIELD.value('(COL_VALUE_AFTER/text())[1]', 'VARCHAR(100)') AS COL_VALUE_AFTER
FROM
    -- 第一层拆分所有IROW节点，锁定单个IROW的独立上下文
    @xml.nodes('/ROOT/IROW') AS T(IRow)
CROSS APPLY
    -- 第二层仅在当前IROW上下文内拆分FIELD节点，保证和上层IROW属性一一对应
    T.IRow.nodes('FIELDS/FIELD') AS F(FIELD)

Oracle 12c+ 等效实现（逻辑一致，仅语法适配）：

SELECT
    XMLCAST(XMLQUERY('$i/COL_ACTION/text()' PASSING T.IRow AS "i" RETURNING CONTENT) AS VARCHAR2(20)) AS COL_ACTION,
    XMLCAST(XMLQUERY('$i/N/text()' PASSING T.IRow AS "i" RETURNING CONTENT) AS NUMBER) AS N,
    XMLCAST(XMLQUERY('$f/COL_NAME_I/text()' PASSING F.FIELD AS "f" RETURNING CONTENT) AS VARCHAR2(50)) AS COL_NAME_I,
    XMLCAST(XMLQUERY('$f/COL_VALUE_AFTER/text()' PASSING F.FIELD AS "f" RETURNING CONTENT) AS VARCHAR2(100)) AS COL_VALUE_AFTER
FROM
    XMLTABLE('/ROOT/IROW' PASSING :xml COLUMNS IRow XMLTYPE PATH '.') T,
    XMLTABLE('FIELDS/FIELD' PASSING T.IRow COLUMNS FIELD XMLTYPE PATH '.') F

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