When XML documents change, such as a fare, a configuration, or a message, you often need to know what changed, or to send only the change instead of the whole document. XMLDIFF compares two XML documents and returns the differences as an xdiff document of operations. XMLPATCH applies such a document to the old version to produce the new one.
Code for This Guide
The main example is in the examples/xml folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
xmldiff(doc1, doc2 [, 'hints']) xmlpatch(document, xdiff_document)
The xdiff document uses the namespace http://xmlns.oracle.com/xdb/xdiff.xsd and contains operations such as update-node, insert-node-before, append-node, and delete-node, each with the XPath of the node it affects.
Find and Apply a Change
The two fare documents differ in the amount. The query reads the operations of the difference with XMLTABLE and patches the old document with them.
Example:
with d as (
select xmltype('<fare><amount>500</amount><cabin>ECONOMY</cabin></fare>') as old_doc,
xmltype('<fare><amount>650</amount><cabin>ECONOMY</cabin></fare>') as new_doc
from dual
)
select x.operation, x.new_value,
xmlserialize(content xmlpatch(old_doc, xmldiff(old_doc, new_doc))) as patched
from d,
xmltable(xmlnamespaces('http://xmlns.oracle.com/xdb/xdiff.xsd' as "xd"),
'/xd:xdiff/*' passing xmldiff(old_doc, new_doc)
columns operation varchar2(20) path 'local-name()',
new_value varchar2(10) path 'xd:content') x;Output:
OPERATION NEW_VALUE PATCHED ______________ ____________ __________________________________________________________ update-node 650 <fare><amount>650</amount><cabin>ECONOMY</cabin></fare>
One update-node operation sets the new amount, and XMLPATCH applied to the old document produces exactly the new one.
Added and Removed Nodes
When an element is removed and another added, the difference contains an operation for each, with the XPath of the position.
Example:
-- a new node and a removed node show up as their own operations
with d as (
select xmltype('<fare><amount>500</amount><cabin>ECONOMY</cabin></fare>') as old_doc,
xmltype('<fare><amount>500</amount><currency>USD</currency></fare>') as new_doc
from dual
)
select x.operation, x.xpath
from d,
xmltable(xmlnamespaces('http://xmlns.oracle.com/xdb/xdiff.xsd' as "xd"),
'/xd:xdiff/*' passing xmldiff(old_doc, new_doc)
columns operation varchar2(20) path 'local-name()',
xpath varchar2(40) path '@xd:xpath') x;Output:
OPERATION XPATH _____________________ ____________________ insert-node-before /fare[1]/cabin[1] delete-node /fare[1]/cabin[1]
The new currency element is inserted before the old cabin element, which is then deleted.
Things to Know
- Store xdiff documents to keep a history of changes that is much smaller than full copies.
- Comparing XML text with = would report a difference for a mere change of spacing; XMLDIFF compares the XML structure.
- For JSON documents, JSON_EQUAL tells whether they differ, and JSON_MERGEPATCH applies changes.
Related Guides
Conclusion
XMLDIFF compares two XML documents and returns their differences as xdiff operations with XPaths, and XMLPATCH applies those operations to produce the new document. Read the operations with XMLTABLE, and store or ship differences instead of whole documents.
