1 2 Previous Next 19 Replies Latest reply on Feb 13, 2019 2:25 AM by heloo

    how can i compare the record

    heloo

      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.

       

      the following record is in table_1:

      <a1 id="1">name1</a1>

      <a2>address1</a2>

      <a2 m="2">address2</a2>

      <a2 m="3">address3</a3>

       

      <a1 id="2">name2</a1>

      <a2>address 4</a2>

      <a3>Female</a3>

       

      <a1 id="3">name3</a1>

      <a2>address 3</a2>

      <a3>Female</a3>

      <a3 m="2">xxx</a3>

       

      the following record is in table_2:

      <a1 id="1">name1</a1>

      <a2>address1111</a2>

      <a2 m="2">address2</a2>

      <a2 m="3">address3333</a3>

       

      <a1 id="2">name2</a1>

      <a2>address 4444</a2>

      <a3>Male</a3>

       

      <a1 id="3">name3</a1>

      <a2>address 3</a2>

      <a3>Female</a3>

      <a3 m="2">xxx</a3>

      .....

      <a3 m="55">yyy</a3>  // *** m is a varible, it is not fixed for each record.

       

      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?)

       

       

       

        • 1. Re: how can i compare the record
          John Thorton

          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

           

          How do I ask a question on the forums?

          • 2. Re: how can i compare the record
            heloo

            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

              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

                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

                  Have a look at XMLDIFF.

                  • 6. Re: how can i compare the record
                    heloo

                    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

                      You can't google 'oracle xmldiff' and click the first link?

                      • 8. Re: how can i compare the record
                        heloo

                        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

                          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

                            how can i return exact node after comparison.

                            • 11. Re: how can i compare the record
                              padders

                              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

                                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

                                  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

                                    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)

                                    1 2 Previous Next