This discussion is archived
7 Replies Latest reply: Sep 13, 2012 7:17 AM by odie_63 RSS

compare 2 different xml versions and highlite the differences

machan Explorer
Currently Being Moderated
Hi,

Currently we have xml and that is displayed on the web page using the css style sheets.
The xml we have remain the same most part but with small changes (say different version created after a week).

We want to be able to track the differences from the previous version and highlight them, the differences, when showing it in the web page.
Please advise if this can be done and how.

Thanks.
  • 1. Re: compare 2 different xml versions and highlite the differences
    AlexAnd Guru
    Currently Being Moderated
    you can get differences by xmldiff then parse derived xml and show
    look at Re: How to find the compare and identify between xmldocs
  • 2. Re: compare 2 different xml versions and highlite the differences
    machan Explorer
    Currently Being Moderated
    Thanks Alex.

    I have tried xmlDiff (modified version).
    declare
      xml1 xmltype := xmltype('
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <REMOVED>abc</REMOVED>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>');
      xml2 xmltype := xmltype('
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <ADDED>xyz</ADDED>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>');
      diff xmltype;
    begin
      diff := dqcs.xmldiff2(xml1, xml2);
      dbms_output.put_line(diff.getclobval(0, 2));
    end;
    this gives me -
    <DIFFERENCES>
      <DIFFERENCE>
        <XPATH><![CDATA[/ROWSET/ROW[EMPNO="7934"]/ADDED]]></XPATH>
        <OPERATION>INSERT</OPERATION>
        <VALUE2><![CDATA[xyz]]></VALUE2>
      </DIFFERENCE>
      <DIFFERENCE>
        <XPATH><![CDATA[/ROWSET/ROW[EMPNO="7934"]/REMOVED]]></XPATH>
        <OPERATION>DELETE</OPERATION>
        <VALUE1><![CDATA[abc]]></VALUE1>
      </DIFFERENCE>
    </DIFFERENCES>
    I am not sure how can I use these results and modify the second xml so that I capture the changes (highlight changes).
    Sorry if this sounds stupid, I haven't worked on xml before.

    Thanks,
  • 3. Re: compare 2 different xml versions and highlite the differences
    machan Explorer
    Currently Being Moderated
    Using this xpath I was able to do some changes to the updated xml
    declare
      xml1 xmltype := xmltype('
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>');
    
      xml2 xmltype := xmltype('
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <ADDED>xyz</ADDED>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>');
    
      diff xmltype;
      xPathExists number;
      nodeValue   varchar2(100);
      xmlValue    varchar2(100);
    
    begin
    
      diff := dqcs.xmldiff2(xml1, xml2);
      dbms_output.put_line(diff.getclobval(0, 2));
      dbms_output.put_line('*********************');
      select existsNode(diff,'/DIFFERENCES/DIFFERENCE/XPATH') into xPathExists from dual;
      dbms_output.put_line(xPathExists);
      SELECT extractValue(diff,'/DIFFERENCES/DIFFERENCE/XPATH') into nodeValue from dual;
      dbms_output.put_line(nodeValue);
      SELECT extractValue(xml2, nodeValue) into xmlValue from dual;
      dbms_output.put_line(xmlValue);
      select updateXML(xml2, nodeValue || '/text()', 'INSERT --- ' || xmlValue || ' --- INSERT') INTO xml2 from dual;
      dbms_output.put_line(xml2.getclobval(0, 2));
      
    end;
    Which gave me -
    <DIFFERENCES>
      <DIFFERENCE>
        <XPATH><![CDATA[/ROWSET/ROW[EMPNO="7934"]/ADDED]]></XPATH>
        <OPERATION>INSERT</OPERATION>
        <VALUE2><![CDATA[xyz]]></VALUE2>
      </DIFFERENCE>
    </DIFFERENCES>
    
    *********************
    1
    /ROWSET/ROW[EMPNO="7934"]/ADDED
    xyz
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <ADDED>INSERT --- xyz --- INSERT</ADDED>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>
    I am able to insert Insert --- --- Insert before and after the value, is there a way I can add a tag. Someting like <Insert> </Insert>.

    Thanks.
  • 4. Re: compare 2 different xml versions and highlite the differences
    AlexAnd Guru
    Currently Being Moderated
    misunderstood

    you can get difference by xmldiff
    then you can parse result xml for your needs

    >
    dqcs.xmldiff2
    >
    ?
    with t as
    (
    select xmltype('<ROWSET>
                      <ROW>
                        <EMPNO>7934</EMPNO>
                        <ENAME>MILLER</ENAME>
                        <JOB>CLERK</JOB>
                        <MGR>7782</MGR>
                        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
                        <REMOVED>abc</REMOVED>
                        <SAL>1300</SAL>
                        <COMM/>
                        <DEPTNO>10</DEPTNO>
                      </ROW>
                    </ROWSET>') xml1,
            xmltype('<ROWSET>
                                  <ROW>
                                    <EMPNO>7934</EMPNO>
                                    <ENAME>MILLER</ENAME>
                                    <JOB>CLERK</JOB>
                                    <MGR>7782</MGR>
                                    <ADDED>xyz</ADDED>
                                    <HIREDATE>1982-01-23T00:00:00</HIREDATE>
                                    <SAL>1300</SAL>
                                    <COMM/>
                                    <DEPTNO>10</DEPTNO>
                                  </ROW>
                                </ROWSET>')  xml2 
    from dual)
    --
    select xmldiff(t.xml1, t.xml2) from t
    give me for 11.1
    <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:insert-node-before xd:node-type="element" xd:xpath="/ROWSET[1]/ROW[1]/HIREDATE[1]">
              <xd:content>
                   <ADDED>xyz</ADDED>
              </xd:content>
         </xd:insert-node-before>
         <xd:delete-node xd:node-type="element" xd:xpath="/ROWSET[1]/ROW[1]/REMOVED[1]"/>
    </xd:xdiff>
    so what do you want to do next?

    sample output and ideas plz
  • 5. Re: compare 2 different xml versions and highlite the differences
    machan Explorer
    Currently Being Moderated
    My first xml -
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>
    Second xml (to be compared) -
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>
    I want to get this result. Basically modifying the second xml, adding a tag <HIGHLIGHT>
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIGHLIGHT>
        <ADDED>xyz</ADDED>
        </HIGHLIGHT>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>
    Edited by: machan on Sep 12, 2012 3:35 PM
  • 6. Re: compare 2 different xml versions and highlite the differences
    AlexAnd Guru
    Currently Being Moderated
    try
    SQL> set serveroutput on 
    SQL> set long 50000
    SQL> 
    SQL> declare
      2  
      3    --main
      4    xml1 xmltype := xmltype('<ROWSET>
      5                                <ROW>
      6                                  <EMPNO>7934</EMPNO>
      7                                  <ENAME>MILLER</ENAME>
      8                                  <JOB>CLERK</JOB>
      9                                  <MGR>7782</MGR>
     10                                  <HIREDATE>1982-01-23T00:00:00</HIREDATE>
     11                                  <REMOVED>abc</REMOVED>
     12                                  <SAL>1300</SAL>
     13                                  <COMM/>
     14                                  <DEPTNO>10</DEPTNO>
     15                                </ROW>
     16                              </ROWSET>');
     17    --new
     18    xml2 xmltype := xmltype('<ROWSET>
     19                                <ROW>
     20                                  <EMPNO>7934</EMPNO>
     21                                  <ENAME>MILLER</ENAME>
     22                                  <JOB>CLERK</JOB>
     23                                  <MGR>7782</MGR>
     24                                  <ADDED>xyz</ADDED>
     25                                  <HIREDATE>1982-01-23T00:00:00</HIREDATE>
     26                                  <SAL>1300</SAL>
     27                                  <COMM/>
     28                                  <DEPTNO>10</DEPTNO>
     29                                </ROW>
     30                              </ROWSET>');
     31    diff xmltype;
     32  
     33    xtype varchar2(80);
     34    xpath varchar2(80);
     35    xpathChild varchar2(80);
     36    xnodeChild varchar2(80);
     37    new_value xmltype;
     38  
     39    new_xml xmltype;
     40  
     41  
     42  begin
     43  
     44    -- get diff
     45    select xmldiff(xml1, xml2) into diff from dual;
     46    dbms_output.put_line(diff.getclobval());
     47  
     48    -- that have
     49    select x.xtype, x.xpath, x.new_value
     50      into xtype, xpath, new_value
     51      from xmltable(
     52             xmlnamespaces('http://xmlns.oracle.com/xdb/xdiff.xsd' as "xd"
     53                          ,'http://www.w3.org/2001/XMLSchema-instance' as "xsi"
     54                          )
     55           , '/xd:xdiff'
     56             passing diff
     57             columns xtype     varchar2(80) path 'local-name(xd:insert-node-before)'
     58                   , xpath     varchar2(80) path'xd:insert-node-before/@xd:xpath'
     59                   , new_value xmltype path 'xd:insert-node-before/xd:content/*'
     60           ) x;
     61  
     62     dbms_output.put_line('type of doing: ' || xtype);
     63     dbms_output.put_line('path for doing: ' || xpath);
     64     dbms_output.put_line('value for doing: ' || new_value.getclobval());
     65  
     66     if (xtype = 'insert-node-before') then
     67       xpathChild := substr(xpath, 1, instr(xpath, '/',-1)-1);
     68       xnodeChild := substr(xpath, instr(xpath, '/',-1)+1);
     69  
     70       select insertChildXMLbefore (xml1,
     71                                    xpathChild,
     72                                    xnodeChild,
     73                                    XMLType('<HIGHLIGHT>' || new_value || '</HIGHLIGHT>'))
     74       into new_xml
     75       from dual;
     76     end if;
     77  
     78  
     79     dbms_output.put_line(new_xml.getclobval());
     80  
     81  end;
     82  /
     
    <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:insert-node-before xd:node-type="element" xd:xpath="/ROWSET[1]/ROW[1]/HIREDATE[1]"><xd:content><ADDED>xyz</ADDED></xd:content></xd:insert-node-before><xd:delete-node xd:node-type="element" xd:xpath="/ROWSET[1]/ROW[1]/REMOVED[1]"/></xd:xdiff>
    type of doing: insert-node-before
    path for doing: /ROWSET[1]/ROW[1]/HIREDATE[1]
    value for doing: <ADDED>xyz</ADDED>
    <ROWSET><ROW><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><MGR>7782</MGR><HIGHLIGHT><ADDED>xyz</ADDED></HIGHLIGHT><HIREDATE>1982-01-23T00:00:00</HIREDATE><REMOVED>abc</REMOVED><SAL>1300</SAL><COMM/><DEPTNO>10</DEPTNO></ROW></ROWSET>
     
    PL/SQL procedure successfully completed
     
    SQL> 
    SQL> select * from v$version where rownum=1;
     
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
     
    SQL> 
  • 7. Re: compare 2 different xml versions and highlite the differences
    odie_63 Guru
    Currently Being Moderated
    What's your database version? (SELECT * FROM v$version)

    On 11.2.0.3, XQuery Update may be useful.

    On lower versions, I would do it like this :
    SQL> WITH sample_data AS (
      2   SELECT xmltype(
      3  '<ROWSET>
      4    <ROW>
      5      <EMPNO>7934</EMPNO>
      6      <ENAME>MILLER</ENAME>
      7      <JOB>CLERK</JOB>
      8      <MGR>7782</MGR>
      9      <HIREDATE>1982-01-23T00:00:00</HIREDATE>
     10      <SAL>1300</SAL>
     11      <COMM/>
     12      <DEPTNO>10</DEPTNO>
     13    </ROW>
     14  </ROWSET>') doc1,
     15  xmltype('<ROWSET>
     16    <ROW>
     17      <EMPNO>7934</EMPNO>
     18      <ENAME>MILLER</ENAME>
     19      <JOB>CLERK</JOB>
     20      <MGR>7782</MGR>
     21      <ADDED>xyz</ADDED>
     22      <HIREDATE>1982-01-23T00:00:00</HIREDATE>
     23      <SAL>1300</SAL>
     24      <COMM/>
     25      <DEPTNO>10</DEPTNO>
     26    </ROW>
     27  </ROWSET>') doc2
     28   FROM dual
     29  )
     30  SELECT XMLSerialize(document
     31           XMLPatch(
     32             doc1
     33           , XMLQuery(
     34              'declare namespace xd = "http://xmlns.oracle.com/xdb/xdiff.xsd"; (: :)
     35               declare function local:copy($itemset as item()*) as item()* {
     36                 for $i in $itemset
     37                  return
     38                   typeswitch($i)
     39                     case element(xd:content) return element {node-name($i)} { <HIGHLIGHT>{local:copy($i/(node()|@*))}</HIGHLIGHT> }
     40                     case element() return element {node-name($i)} { local:copy($i/(node()|@*)) }
     41                     default return $i
     42               }; local:copy(*)'
     43               passing XMLDiff(doc1, doc2)
     44               returning content
     45             )
     46           )
     47           as clob indent
     48         )
     49  FROM sample_data
     50  ;
     
    XMLSERIALIZE(DOCUMENTXMLPATCH(
    --------------------------------------------------------------------------------
    <ROWSET>
      <ROW>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIGHLIGHT>
          <ADDED>xyz</ADDED>
        </HIGHLIGHT>
        <HIREDATE>1982-01-23T00:00:00</HIREDATE>
        <SAL>1300</SAL>
        <COMM/>
        <DEPTNO>10</DEPTNO>
      </ROW>
    </ROWSET>
     
    Basically, we take the output of XMLDiff and modify it slightly to insert the <HIGHLIGHT> tag. Then we just have to call XMLPatch function with that newly formed Xdiff document.

    Note that I would normally use XSLT (identity-based template) to modify the Xdiff output but there seems to be a bug when dealing with processing-instruction() node, hence my using the XQuery equivalent.

    Edited by: odie_63 on 13 sept. 2012 16:14

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points