'with XMLDIFF, how to compare only the fields that my xml elements have in common?

introduction:

I have query using a pipeline function. I won't change the names of the returned columns but I will add other columns.

I want to compare the result of the old query with the new query (syntaxal always the same (select * from mypipelinefunction) , but I have changed the pipeline function )

I have used "select *" instead of "select the name of the columns" because there is a lot names.

code:

the code example is simplified to focus on the problem addressed in the title. (no pipeline function. Only two "identic" queries are tested. The second query has one more column that the first.

SELECT 
    XMLDIFF (
        XMLTYPE.createXML (
            DBMS_XMLGEN.getxml ('select 1 one, 2 two from dual')),
    XMLTYPE.createXML (
            DBMS_XMLGEN.getxml ('select 1 one from dual'))) 
from dual.

I want that XMLDIFF to say that there is no difference because the only columns that I care about are the colums that are in common.

In short I would like to have this result

<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

</xd:xdiff>

instead of this result

<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><xd:delete-node xd:node-type="element" xd:xpath="/ROWSET[1]/ROW[1]/TWO[1]"/></xd:xdiff>

Is this possible to force XMLdiff to compare only the columns that are in commun?

code

Another way to fix this problem would be to have a shortcut in TOAD that transform select * from t in select first_column, ......last_column from t. And it should work even if t is a pipeline function

sqlxmloracledockermariadb


Solution 1:[1]

If you only care about certain columns then wrap your query in a outer-query to only output the columns you care about:

SELECT XMLDIFF (
         XMLTYPE.createXML (
           DBMS_XMLGEN.getxml (
             'SELECT one FROM (select 1 one, 2 two from dual)'
           )
         ),
         XMLTYPE.createXML (
           DBMS_XMLGEN.getxml (
             'SELECT one FROM (select 1 one from dual)'
           )
         )
       ) AS diff
FROM   DUAL;

Which outputs:

DIFF
<xd:xdiff xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><?oracle-xmldiff operations-in-docorder="true" output-model="snapshot" diff-algorithm="global"?></xd:xdiff>

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0

Related Questions