-
1. Re: how can i compare the record
John Thorton Feb 3, 2019 2:31 PM (in response to heloo)heloo wrote:
i am using oracle 12c. and the script may execute in sqlplus
i have xmltype. which contain all the nodes and some node with attribute. but the no. of attribute is not fixed. it is variable. each record contains the node with different no. of attribute.
i would like to compare the different between two tables and return the difference in result set. how can i do that?
(it is because i am going to update a amount data, and i would like to compare the different before update and after update, any advises?)
TABLES?
What Tables?
I don't see any tables.
BTW tables do NOT have any records; only rows
Post CREATE TABLE statements for both tables.
What is expected & desired results from the comparison?
Please click on URL below & provide details as stated in #5 - #9 inclusive
-
2. Re: how can i compare the record
heloo Feb 3, 2019 4:42 PM (in response to John Thorton)i have 2 tables with same column name and same structure, there are 2 column in table (id as number, xmlcolumn as xmltype), i would like to compare the content of xmlcolumn between 2 tables and only return the different data in result.
create table table_1 (
id number (30),
xmlcolumn xmltype
)
create table table_2 (
id number (30),
xmlcolumn xmltype
)
xmlcolumn in table_1
---------------------------------------------
id=1
xmlcolumn:
<a1 id="1">name1</a1>
<a2>address1</a2>
<a2 m="2">address2</a2>
id=2
xmlcolumn:
<a1 id="2">name2</a1>
<a2>address 4</a2>
<a3>Female</a3>
id=3
xmlcolumn:
<a1 id="3">name3</a1>
<a2>address 3</a2>
<a3>Female</a3>
<a3 m="2">xxx</a3>
xmlcolumn in table_2:
--------------------------------------
id=1
xmlcolumn:
<a1 id="1">name1</a1>
<a2>address1111</a2>
<a2 m="2">address2</a2>
<a2 m="3">address3333</a3>
id=2
xmlcolumn:
<a1 id="2">name2</a1>
<a2>address 4444</a2>
<a3>Male</a3>
id=3
xmlcolumn:
<a1 id="3">name3</a1>
<a2>address 3</a2>
<a3>Female</a3>
<a3 m="2">xxx</a3>
<a3 m="55">yyy</a3>
output
---------------------------------------------
id diff record in t1 diff record in t2
1 <a2>address1</a2> <a2>address1111</a2>
1 null <a2 m="3">address3333</a3>
2 <a2>address 4</a2> <a2>address 4444</a2>
2 <a3>Female</a3> <a3>Male</a3>
3 null <a3 m="55">yyy</a3>
-
3. Re: how can i compare the record
heloo Feb 3, 2019 4:45 PM (in response to heloo)i have xmltype column to contain the xml record, there are so many node in xmltype column, now i would like to update the xmltype column, after i update the data, i would like to compare before and after update and return to result set.
any suggestion that can do that? any methods?
-
4. Re: how can i compare the record
heloo Feb 6, 2019 7:45 AM (in response to heloo)anyone knows how to compare the content and output the different per row between two tables (two tables with same table structure which has only 2 columns, first one is record ID, and second one is xmltype column which contain xml record).
how to prepare script to compare the difference xmltype column between 2 tables in same database oracle 12c? any output which can output the difference is okay.
-
5. Re: how can i compare the record
padders Feb 6, 2019 10:26 AM (in response to heloo)Have a look at XMLDIFF.
-
6. Re: how can i compare the record
heloo Feb 6, 2019 10:28 AM (in response to padders)any sample script for reference.
the following is the table column. i would like to compare xmlcol between 2 tables and output only different node (include node with attribute, <a1 m="2'>)
my_table
id varchar(100)
xmlcolumn xmltype
-
7. Re: how can i compare the record
padders Feb 6, 2019 10:34 AM (in response to heloo)You can't google 'oracle xmldiff' and click the first link?
-
8. Re: how can i compare the record
heloo Feb 8, 2019 7:39 AM (in response to padders)I have tried, each record will return the following content.
<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>
-
9. Re: how can i compare the record
heloo Feb 8, 2019 8:00 AM (in response to padders)i have two table , each table contain xmltype column. now i am going to compare the difference between 2 xmltype column.
the difference is only happened in node <a150 m="57">, but it return ="/row[1]/a150[17]/text()[1]" by using XMLDIFF, why?
<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="/row[1]/a150[17]/text()[1]"><xd:content>20181231</xd:content></xd:update-node></xd:xdiff>
-
10. Re: how can i compare the record
heloo Feb 8, 2019 8:01 AM (in response to heloo)how can i return exact node after comparison.
-
11. Re: how can i compare the record
padders Feb 8, 2019 8:34 AM (in response to heloo)You will need to examine the format xmldiff returns for each difference type and deal with them separately. I do not have and have not seen a full example of this before you ask :-D
So, in your example, for 'xd:diff' child nodes of type 'xd:update-node' and xd:node-type="text" the 'xd:xpath' element gives you the xpath of the changed text changed (/row[1]/a150[17]/text()[1]).
You can just apply this xpath to the original XML(s) to get the old/new value using XMLQUERY as you have seen in previous threads, e.g.
SELECT XMLSERIALIZE (
CONTENT XMLQUERY (
'/a[1]/a2[1]/text()[1]'
PASSING xmltype (
'<a><a1 id="1">name1</a1><a2>address1111</a2><a2 m="2">address2</a2></a>')
RETURNING CONTENT) AS CLOB
INDENT)
xml
FROM DUAL;
address1111
If however you want the node(s) containing this value, you can modify the 'xd:xpath' using standard xpath syntax such as by adding '/..' to the end of the xpath(there may well be other ways of expressing this) to get the node, e.g.
SELECT XMLSERIALIZE (
CONTENT XMLQUERY (
'/a[1]/a2[1]/text()[1]/..'
PASSING xmltype (
'<a><a1 id="1">name1</a1><a2>address1111</a2><a2 m="2">address2</a2></a>')
RETURNING CONTENT) AS CLOB
INDENT)
xml
FROM DUAL;
<a2>address1111</a2>
-
12. Re: how can i compare the record
heloo Feb 12, 2019 6:49 AM (in response to padders)i can't dynamiclly to check the node before and after updae node value.
i want to check the whole xml and see the different. how can i perform that to compare the whole xml and only return the different per record.
-
13. Re: how can i compare the record
padders Feb 12, 2019 8:46 AM (in response to heloo)Who said anything about dynamically doing anything?
As far as I can tell you want to compare two XML documents and report the changes.
XMLDIFF returns multiple difference nodes, extract them with XMLTABLE as you have been shown multiple times in other threads and handle each difference node (for 'xd:update-node' example is above).
-
14. Re: how can i compare the record
heloo Feb 12, 2019 4:09 PM (in response to padders)sample data - table 1
id xmlcolumn
1 <a1>1</a1><a2>e</a2><a3>20181231</a3>
2 <a1>a</a1><a2>20181231</a2><a3>1</a3><a3 m="2">2</a3><a3 m="3">20181231</a3>
3 <a1>a</a1>ee<a2></a2><a3>20181231</a3><a3 m="2">2</a3><a3 m="3">x</a3><a3 m="4">cc</c4><a3 m="5">20181231</c4>
4 <a1>20181231</a1><a2>4</a2><a3>20181231</a3>
5 <a1>1</a1><a2></a2><a3>20181231</a3>
--
sample data - table 2
id xmlcolumn
1 <a1>1</a1><a2>c</a2><a3>20181231</a3><a5>111</a5>
2 <a1>a</a1>ee<a2></a2><a3>20181231</a3><a3 m="2">2</a3><a3 m="3">x</a3><a3 m="4">cc</c4><a3 m="5">20181231</c4>
3 <a1>a</a1><a2>20181231</a2><a3>1</a3><a3 m="2">2</a3><a3 m="3">20181231</a3>
4 <a1>20181231</a1><a2>4</a2><a3>20181231</a3>
5 <a1>1</a1><a2></a2><a3>20181231</a3><a4>xx</a4>
i never know that the total no. of node for each node <a1>, <a2>, <a3>.....n
as it is not fixed and each record will have different no. of nodes like sample data above. (xmlcolumn structure is not same in 2 tables)