4 Replies Latest reply: Feb 4, 2012 9:00 AM by odie_63 RSS

    How to find the compare and identify between xmldocs

    912973
      Hi

      Is there any way to compare and find the difference in the elemnt between two elements using sql xquery.

      ex:
      a.xml(string datatype)

      <xml>
      <instrumentID>100</instrumentID>
      <entityId>
      <TeId>20</TeId>
      </entityID>
      </xml>

      b.xml
      <xml>
      <instrumentID>100</instrumentID>
      <entityId>
      <TeId>10</TeId>
      </entityID>
      </xml>

      Both should be compare so that to find the difference in field of two xml docs and identify the differece

      o/p:

      Diff.xml
      old changed
      TeId 10 20



      Thanks
      oracle:11g version
        • 1. Re: How to find the compare and identify between xmldocs
          damorgan
          Are you asking about the XMLDIFF function?
          http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions241.htm#SQLRF20025
          • 2. Re: How to find the compare and identify between xmldocs
            odie_63
            XMLDiff function will generate an XDiff instance document describing the differences :
            SQL> with xmldocs as (
              2   select xmltype('<root>
              3   <instrumentID>100</instrumentID>
              4   <entityId><TeId>20</TeId></entityId>
              5  </root>') as a,
              6          xmltype('<root>
              7   <instrumentID>100</instrumentID>
              8   <entityId><TeId>10</TeId></entityId>
              9  </root>') as b
             10   from dual
             11  )
             12  select xmlserialize(document xmldiff(a, b) as clob indent)
             13  from xmldocs
             14  ;
            
            XMLSERIALIZE(DOCUMENTXMLDIFF(A,B)ASCLOBINDENT)
            ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            <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:update-node xd:node-type="text" xd:xpath="/root[1]/entityId[1]/TeId[1]/text()[1]">
                <xd:content>10</xd:content>
              </xd:update-node>
            </xd:xdiff>
            You can feed that result to an XMLTable in order to present before/after values.
            • 3. Re: How to find the compare and identify between xmldocs
              912973
              but this doesn't give me which element has got changed i.e name of the element and its value would be helpful.
              • 4. Re: How to find the compare and identify between xmldocs
                odie_63
                Yes, that's why I said you can use XMLTable to further analyze the XDiff document.

                For example, if you only expect element values to change :
                SQL> variable doc1 clob
                SQL> variable doc2 clob
                SQL> begin
                  2   :doc1 := '<root>
                  3   <instrumentID>100</instrumentID>
                  4   <entityId><TeId>20</TeId></entityId>
                  5  </root>';
                  6  end;
                  7  /
                
                PL/SQL procedure successfully completed.
                
                SQL> begin
                  2   :doc2 := '<root>
                  3   <instrumentID>102</instrumentID>
                  4   <entityId><TeId>10</TeId></entityId>
                  5  </root>';
                  6  end;
                  7  /
                
                PL/SQL procedure successfully completed.
                
                SQL> select xmlcast(xmlquery(x.xpath passing xmltype(:doc1) returning content) as varchar2(30)) as "ElementName"
                  2       , x.new_value as "NewValue"
                  3  from xmltable(
                  4         xmlnamespaces('http://xmlns.oracle.com/xdb/xdiff.xsd' as "xd")
                  5       , '/xd:xdiff/xd:update-node'
                  6         passing xmldiff(xmltype(:doc1), xmltype(:doc2))
                  7         columns xpath     varchar2(80) path 'concat("name(",@xd:xpath,"/..)")'
                  8               , new_value varchar2(30) path 'xd:content'
                  9       ) x
                 10  ;
                
                ElementName                    NewValue
                ------------------------------ ------------------------------
                instrumentID                   102
                TeId                           10
                 
                Or with a regular expression to extract the element name directly from the XPath address :
                SQL> select x.element_name as "ElementName"
                  2       , x.new_value as "NewValue"
                  3  from xmltable(
                  4         xmlnamespaces('http://xmlns.oracle.com/xdb/xdiff.xsd' as "xd")
                  5       , '/xd:xdiff/xd:update-node'
                  6         passing xmldiff(xmltype(:doc1), xmltype(:doc2))
                  7         columns element_name varchar2(30) path 'ora:replace(@xd:xpath,".*/(.*)\[\d+\]/.*$","\1")'
                  8               , new_value    varchar2(30) path 'xd:content'
                  9       ) x
                 10  ;
                
                ElementName                    NewValue
                ------------------------------ ------------------------------
                instrumentID                   102
                TeId                           10