如何在PL/SQL中按指定字符串顺序重新排序XML节点

现有如下XML片段：

<ArrayOfItemsElement>
    <ItemsElement>
        <code>92100141</code>
        <description>BLABLA</description>
        <number>1</number>
        <value>10</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
    <ItemsElement>
        <code>92200118</code>
        <description>BBBBBB</description>
        <number>1</number>
        <value>999</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
    <ItemsElement>
        <code>92100999</code>
        <description>TESTEST</description>
        <number>1</number>
        <value>10</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
    <ItemsElement>
        <code>92200118</code>
        <description>AAAAAAAA</description>
        <number>1</number>
        <value>15</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
</ArrayOfItemsElement>

另有指定XML元素顺序的字符串："92200118;92200118;92100141;92100999"，要求在PL/SQL中根据该字符串的指定顺序重新排序上述XML节点，生成新的XML片段（注：code为92200118的两个元素顺序可任意）。

可以通过以下PL/SQL实现需求，核心逻辑是将顺序字符串拆分为带位置序号的列表，关联XML节点后按指定顺序重组：

DECLARE
    v_input_xml XMLTYPE := XMLTYPE('
<ArrayOfItemsElement>
    <ItemsElement>
        <code>92100141</code>
        <description>BLABLA</description>
        <number>1</number>
        <value>10</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
    <ItemsElement>
        <code>92200118</code>
        <description>BBBBBB</description>
        <number>1</number>
        <value>999</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
    <ItemsElement>
        <code>92100999</code>
        <description>TESTEST</description>
        <number>1</number>
        <value>10</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
    <ItemsElement>
        <code>92200118</code>
        <description>AAAAAAAA</description>
        <number>1</number>
        <value>15</value>
        <taxPercentage>0</taxPercentage>
        <currencyCode>EUR</currencyCode>
    </ItemsElement>
</ArrayOfItemsElement>');
    v_order_str VARCHAR2(100) := '92200118;92200118;92100141;92100999';
    v_order_tab DBMS_UTILITY.LNAME_ARRAY;
    v_count PLS_INTEGER;
    v_result_xml XMLTYPE;
BEGIN
    -- 拆分顺序字符串为数组
    DBMS_UTILITY.COMMA_TO_TABLE(REPLACE(v_order_str, ';', ','), v_count, v_order_tab);
    
    -- 生成排序后的XML节点集合
    SELECT XMLAGG(
        x.item ORDER BY pos.seq
    ) INTO v_result_xml
    FROM (
        -- 为每个顺序项分配序号
        SELECT COLUMN_VALUE AS code, ROWNUM AS seq
        FROM TABLE(v_order_tab)
    ) pos
    JOIN (
        -- 提取XML节点并为同code的节点标记行号
        SELECT 
            xt.code,
            xt.item,
            ROW_NUMBER() OVER(PARTITION BY xt.code ORDER BY NULL) AS rn
        FROM XMLTABLE(
            '/ArrayOfItemsElement/ItemsElement'
            PASSING v_input_xml
            COLUMNS
                code VARCHAR2(20) PATH 'code',
                item XMLTYPE PATH '.'
        ) xt
    ) x ON pos.code = x.code
    -- 匹配同code节点的序号，避免重复匹配混乱
    AND ROW_NUMBER() OVER(PARTITION BY pos.code ORDER BY NULL) = x.rn;
    
    -- 包裹根节点生成最终XML
    v_result_xml := XMLTYPE('<ArrayOfItemsElement>' || v_result_xml.getclobval() || '</ArrayOfItemsElement>');
    
    -- 输出结果（可替换为存储到变量/表等逻辑）
    DBMS_OUTPUT.PUT_LINE(v_result_xml.getclobval());
END;
/

关键步骤说明 #

• 拆分顺序字符串：利用DBMS_UTILITY.COMMA_TO_TABLE将分号分隔的字符串转为数组，同时为每个项分配顺序序号。
• 解析XML节点：通过XMLTABLE提取XML中的每个ItemsElement节点及其code值，用ROW_NUMBER()为同code的节点标记行号，解决重复code的匹配问题。
• 关联排序：将顺序列表与XML节点关联，按顺序列表的序号聚合节点，最后包裹根节点得到目标XML。

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