2 Replies Latest reply: Mar 22, 2013 8:55 AM by KnightOfBlueArmor RSS

    Replacing nodes with duplicates

    KnightOfBlueArmor
      I'm having trouble figuring out how to write an XQuery that can essentially "skip" a replace when I tell it to. I'm sure that doesn't make any sense, so here is a better explanation.

      I have an XML file stored in an Oracle XML DB that looks like this:
      <bookstore>
          <book title="Analytical Chemistry" publish_date="198710">
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Particle Physics" publish_date="199202">
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Applied Biophysics" publish_date="201005">
             <author>...
             <publisher>....
             <etc.>
          </book>
      </bookstore>
      We get update orders that look like the same thing, with a "command" attached to them to either add the book to the store or drop it:
      <bookstore>
          <book title="Analytical Chemistry" publish_date="199210">
             <order>merge</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Particle Physics" publish_date="199202">
             <order>drop</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Chaos Theory" publish_date="199102">
             <order>merge</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
      </bookstore>
      The rule is that I should replace or insert a book in the store if I have a book with the same title, and the order is to "merge", or drop it if the order is to "drop". I have an XQuery updating function now that does this which looks like this:
      declare updating function local:book_merge($old, $book_merge)
      {
           for $orderEntr in $book_merge/book
           let $chgCd := $orderEntr/order
           let $orderTitle := $orderEntr/@title
              let $oldBooks := $old/bookstore
           let $oldBookEntr := $oldBooks/book[@title=$orderTitle]
           return
              if( $chgCd = "drop" and exists($oldBookEntr) )
              then delete node $oldBookEntr
              else if( exists($oldBookEntr) )
              then replace node $oldBookEntr with $orderEntr
              else insert node $orderEntr into $oldBooks
      };
      This works fine for orders like the above, but every once in a while, we get an order like this:
      <bookstore>
         <book title="Analytical Chemistry" publish_date="199210">
             <order>merge</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Analytical Chemistry" publish_date="199210">
             <order>merge</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Particle Physics" publish_date="199202">
             <order>drop</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
          <book title="Chaos Theory" publish_date="199102">
             <order>merge</order>
             <author>...
             <publisher>....
             <etc.>
          </book>
      </bookstore>
      Notice how the first entry is duplicated. In this situation, the requirement is to only enter the book into the bookstore once. Can anyone give me an idea of how I might go about accomplishing that? I'm struggling to understand how to ask the right question, so please forgive me.
        • 1. Re: Replacing nodes with duplicates
          odie_63
          You can add a predicate that'll filter out the following (or preceding) duplicate entries of a given entry :
          where not($orderEntr/following-sibling::book[@title = $orderTitle])
          Complete test case :
          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options
          
          SQL> create table tmp_xml of xmltype;
          
          Table created.
          
          SQL> insert into tmp_xml values (
            2  xmltype('<bookstore>
            3      <book title="Analytical Chemistry" publish_date="198710">
            4         <author>XXX</author>
            5         <publisher></publisher>
            6      </book>
            7      <book title="Particle Physics" publish_date="199202">
            8         <author>YYY</author>
            9         <publisher></publisher>
           10      </book>
           11      <book title="Applied Biophysics" publish_date="201005">
           12         <author>ZZZ</author>
           13         <publisher></publisher>
           14      </book>
           15  </bookstore>')
           16  );
          
          1 row created.
          
          SQL> var orders clob
          SQL> begin
            2
            3   :orders := '<bookstore>
            4      <book title="Analytical Chemistry" publish_date="199210">
            5         <order>merge</order>
            6         <author>XXX</author>
            7         <publisher>123</publisher>
            8      </book>
            9      <book title="Analytical Chemistry" publish_date="199210">
           10         <order>merge</order>
           11         <author>XXX</author>
           12         <publisher>123</publisher>
           13      </book>
           14      <book title="Particle Physics" publish_date="199202">
           15         <order>drop</order>
           16         <author>YYY</author>
           17         <publisher></publisher>
           18      </book>
           19      <book title="Chaos Theory" publish_date="199102">
           20         <order>merge</order>
           21         <author>ZZZ-2</author>
           22         <publisher></publisher>
           23      </book>
           24  </bookstore>';
           25
           26  end;
           27  /
          
          PL/SQL procedure successfully completed.
          
          SQL> set long 5000
          SQL> set pages 100
          SQL>
          SQL> select /*+ no_xml_query_rewrite */
            2         xmlserialize(document
            3           xmlquery(
            4           'copy $d := $old
            5            modify (
            6              for $orderEntr in $orders/bookstore/book
            7              let $chgCd := $orderEntr/order
            8              let $orderTitle := $orderEntr/@title
            9              let $oldBooks := $d/bookstore
           10              let $oldBookEntr := $oldBooks/book[@title = $orderTitle]
           11              where not($orderEntr/following-sibling::book[@title = $orderTitle])
           12              return
           13                 if( $chgCd = "drop" and exists($oldBookEntr) )
           14                   then delete node $oldBookEntr
           15                 else if( exists($oldBookEntr) )
           16                        then replace node $oldBookEntr with $orderEntr
           17                      else insert node $orderEntr into $oldBooks
           18            )
           19            return $d'
           20           passing object_value as "old"
           21                 , xmlparse(document :orders) as "orders"
           22           returning content
           23           )
           24           indent
           25         ) as result
           26  from tmp_xml ;
          
          RESULT
          --------------------------------------------------------------------------------
          <bookstore>
            <book title="Analytical Chemistry" publish_date="199210">
              <order>merge</order>
              <author>XXX</author>
              <publisher>123</publisher>
            </book>
            <book title="Applied Biophysics" publish_date="201005">
              <author>ZZZ</author>
              <publisher/>
            </book>
            <book title="Chaos Theory" publish_date="199102">
              <order>merge</order>
              <author>ZZZ-2</author>
              <publisher/>
            </book>
          </bookstore>
          It's another question you probably already have the answer : I suppose you also need to remove the <order> nodes from the newly merged books ?
          • 2. Re: Replacing nodes with duplicates
            KnightOfBlueArmor
            Believe it or not, no. I have no idea why they do things this way, but they do.
            That saved me a LOT of work. I spent all morning trying to figure out a functional way of doing this using recursion. I'm going to give this a try right now. Thanks odie!