Reorder XML nodes in PL\SQL-CodePudding

I have this XML fragment:

<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>

I have a string with the specific order for each element in this XML: "92200118;92200118;92100141;92100999".

In PL/SQL, how can I reorder the XML above using the specific order in the string and get a new XML fragment. Note: code 92200118 appears two times in the fragment. Can be one or the other at first.

Thanks.

CodePudding user response：

With a query you could decompose, order and recompose your XML document as follows:

Having

create table test_xml (id int, xmldata xmltype);
insert into test_xml values (1, 
'<ArrayOfItemsElement>
    <ItemsElement>...
 </ItemsElement>
</ArrayOfItemsElement>');

Then

with table1 as (
    SELECT t.* FROM test_xml, XMLTABLE('/ArrayOfItemsElement/ItemsElement' 
    PASSING xmldata COLUMNS
        "code" VARCHAR2(100) PATH 'code',
        "description" VARCHAR2(100) PATH 'description',
        "number" VARCHAR2(100) PATH 'number',
        "value" VARCHAR2(100) PATH 'value',
        "taxPercentage" VARCHAR2(100) PATH 'taxPercentage',
        "currencyCode" VARCHAR2(100) PATH 'currencyCode') t
    order by decode("code", '92200118', 1, '92100141', 2, '92100999', 3))--custom order
select xmlelement("ArrayOfItemsElement" , xmlagg(xmlelement("ItemsElement", 
    xmlelement("code", "code") , 
    xmlelement("description", "description") ,
    xmlelement("number", "number") ,
    xmlelement("value", "value") ,
    xmlelement("taxPercentage", "taxPercentage") ,
    xmlelement("currencyCode", "currencyCode")
    ))) as xmldata from table1;

If you prefer PL/SQL it would be like

DECLARE 
    j test_xml%rowtype;    
    document VARCHAR2(1000);
    xml_document xmltype;
BEGIN  
    document := '<ArrayOfItemsElement>';
    FOR j IN (SELECT t.* FROM test_xml, XMLTABLE('/ArrayOfItemsElement/ItemsElement' 
    PASSING xmldata COLUMNS
        "code" VARCHAR2(100) PATH 'code',
        "description" VARCHAR2(100) PATH 'description',
        "number" VARCHAR2(100) PATH 'number',
        "value" VARCHAR2(100) PATH 'value',
        "taxPercentage" VARCHAR2(100) PATH 'taxPercentage',
        "currencyCode" VARCHAR2(100) PATH 'currencyCode') t
    order by decode("code", '92200118', 1, '92100141', 2, '92100999', 3), "description")
    LOOP
        --reassemble xml_document
        document := document || '<ItemsElement>' || 
            '<code>' || j."code" || '</code>' || 
            '<description>' || j."description" || '</description>' || 
            '<number>' || j."number" || '</number>' || 
            '<value>' || j."value" || '</value>' || 
            '<taxPercentage>' || j."taxPercentage" || '</taxPercentage>' || 
            '<currencyCode>' || j."currencyCode" || '</currencyCode>' || 
            '</ItemsElement>';       
    END LOOP;
    document := document || '</ArrayOfItemsElement>';
    xml_document := xmltype(document);
    --insert into sample table
    insert into test_xml values (2, xml_document);    
END;

CodePudding user response：

I get manage one possible solution for this issue. I can transform XML in a inline view. Order that inline view using the string with order values and then transform again in a XML.

SELECT XMLELEMENT (
           "ArrayOfItemsElement",
           XMLAGG (
               XMLELEMENT (
                   "ItemsElement",
                   XMLCONCAT (
                       XMLSequenceType (
                           xml_utils.getXmlTag (pv_tagName => 'code', pv_value => code),
                           xml_utils.getXmlTag (pv_tagName => 'description', pv_value => description),
                           xml_utils.getXmlTag (pv_tagName => 'number', pv_value => quantity),
                           xml_utils.getXmlTag (pv_tagName => 'VALUE', pv_value => VALUE),
                           xml_utils.getXmlTag (pv_tagName   => 'taxPercentage',
                                                pv_value     => taxPercentage),
                           xml_utils.getXmlTag (pv_tagName   => 'currencyCode',
                                                pv_value     => currencyCode))))))
  FROM (SELECT DISTINCT code,
                        description,
                        quantity,
                        VALUE,
                        taxPercentage,
                        currencyCode
          FROM (          SELECT code,
                                 description,
                                 quantity,
                                 VALUE,
                                 taxPercentage,
                                 currencyCode,
                                 ROW_NUMBER () OVER (ORDER BY a.COLUMN_VALUE DESC) row_num
                            FROM XMLTABLE ('ArrayOfItemsElement/ItemsElement'
                                           PASSING 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>')
                                           COLUMNS code             VARCHAR2 (10) PATH 'code',
                                                   description      VARCHAR2 (50) PATH 'description',
                                                   quantity         VARCHAR2 (2) PATH 'number',
                                                   VALUE            VARCHAR2 (10) PATH 'value',
                                                   taxPercentage    VARCHAR2 (5) PATH 'taxPercentage',
                                                   currencyCode     VARCHAR2 (3) PATH 'currencyCode') x,
                                 TABLE (Utils.SPLIT ('92200118;92200118;92200118;92100141;92100999', ';')) a
                           WHERE a.COLUMN_VALUE = x.code
                        ORDER BY row_num));

Note: Utils.Split is a function that creates a table using ';' as a separator. xml_utils.getXmlTag create a XML tag with the value assigned.

Just check!