1 2 Previous Next 22 Replies Latest reply on Mar 24, 2019 5:35 PM by EdStevens

    how can i compare the record

    heloo
    heloo Feb 3, 2019 10:31 AM

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

       

       

       

      • 402 Views
      • Tags:
        • 1. Re: how can i compare the record
          John Thorton
          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

           

          How do I ask a question on the forums?

          • 2. Re: how can i compare the record
            heloo
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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)

                                    1 2 Previous Next