7 Replies Latest reply: Sep 13, 2012 9:17 AM by odie_63 RSS

    compare 2 different xml versions and highlite the differences

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