4 Replies Latest reply: Mar 13, 2012 9:12 AM by odie_63 RSS

    XQuery Update (11.2.0.3) and the path table

    917188
      Hi,

      I'm currently using XQuery update on 11.2.0.3 to merge 2 XML documents, having tried a number of approaches up to now on 11.2.0.2.

      My table has one XMLType column (securefile binary containing unstructured xml) which has XML deltas applied to it via a stored procedure.

      An unstructured XML index is defined on the column.

      Previously, I was doing the following:
      1) Merge documents using XMLQuery; only produce a merged document if changes were detected
      2) Update the table if the merged document is non-null

      i.e.
      select XMLQuery(<merging code>) passing oldDoc as "oldDoc", delta as "delta" returning content) into mergedDoc from dual;
      if (mergedDoc is not null) then 
          update myTable set xmlDoc = mergedDoc where id=v_id;
      end if;
      However, this replaces the entire document if any changes are detected (with an obvious impact on the path table).

      I then read (UpdateXML query rewrite with unstructured data? about the use of XQuery Update in 11.2.0.3 to only target the affected document fragments so my current approach is:
      update myTable
      set xmldoc = 
              xmlquery('declare namespace m="http://www.blah.com/merge";
              (: See if two nodes have matching text (or no text which is an empty sequence :)
              declare function m:matchText($one as element(), $two as element()) as xs:boolean {
                  if ($one/text() = $two/text() or count($one/text())=0 and count($two/text())=0) then true() else false()
              };
              declare updating function m:merge($old as element()?, $new as element()?) {
                  (for $o in $old/* return
                      for $n in $new/* where name($o) = name($n) return
                          if (count($n/*) > 0) then
                              m:merge($o, $n)
                          else
                              if (m:matchText($n, $o)) then () else replace value of node $o with $n,
                      for $n in $new/* where (not(some $o in $old/* satisfies name($o) = name($n))) return insert node $n into $old)
              };
              copy $d := .
              modify (
                  (m:merge($d/*,$delta/*))
              ) return $d'
              passing xmldoc, delta as "delta"
              returning content)
      where id = v_id;
      If no changes were detected between the documents (i.e. the XQuery update did not actually modify the document), would you expect this statement to have no impact on the path table? Is there any way to get more trace information which shows what the update is actually doing when it executes?

      Apologies if this is overlong or if I have omitted any supplementary information.

      Thanks for any assistance
      Larry
        • 1. Re: XQuery Update (11.2.0.3) and the path table
          917188
          I set the following event:

          alter session set events='19027 trace name context forever, level 0x2000'

          and ran an update using test data.

          The following was written to a trace file:

          *** 2012-03-12 17:30:08.671
          xvm-inp 5
          --------- XQuery NO rewrt expr END-----
          --------- XQuery NO rewrt expr BEG-----
          xvm-inp 5
          --------- XQuery NO rewrt expr END-----
          --------- XQuery NO rewrt expr BEG-----
          not delete/insert/replace in modify clause
          --------- XQuery NO rewrt expr END-----
          --------- XQuery NO rewrt expr BEG-----
          not delete/insert/replace in modify clause
          --------- XQuery NO rewrt expr END-----
          --------- XQuery NO rewrt expr BEG-----
          UDF with noderef
          --------- XQuery NO rewrt expr END-----

          So I assume the XPath rewrite is not happening. If so, is it because I have a function call within my "modify"?
          • 2. Re: XQuery Update (11.2.0.3) and the path table
            odie_63
            So I assume the XPath rewrite is not happening. If so, is it because I have a function call within my "modify"?
            Precisely, because the function is recursive and cannot be rewritten using SQL operators.

            I'd be interested in a test case.
            Could you give sample documents (both input and delta) and the expected result?

            Did you try the XSLT approach too?
            • 3. Re: XQuery Update (11.2.0.3) and the path table
              917188
              Hi,

              many thanks for your reply.

              A simple testcase is below. The recursive approach is required because the documents may be hierarchial.
              create table mytable (id number(19,0), xmldoc xmltype) xmltype column "XMLDOC" store as securefile binary xml;
              insert into myTable values (1,xmltype('<xml><el1>1</el1><el2>2</el2><el3>3</el3></xml>'));
              update myTable
              set xmldoc = 
                      xmlquery('declare namespace m="http://www.blah.com/merge";
                      (: See if two nodes have matching text (or no text which is an empty sequence :)
                      declare function m:matchText($one as element(), $two as element()) as xs:boolean {
                          if ($one/text() = $two/text() or count($one/text())=0 and count($two/text())=0) then true() else false()
                      };
                      declare updating function m:merge($old as element()?, $new as element()?) {
                          (for $o in $old/* return
                              for $n in $new/* where name($o) = name($n) return
                                  if (count($n/*) > 0) then
                                      m:merge($o, $n)
                                  else
                                      if (m:matchText($n, $o)) then () else replace value of node $o with $n,
                              for $n in $new/* where (not(some $o in $old/* satisfies name($o) = name($n))) return insert node $n into $old)
                      };
                      copy $d := .
                      modify (
                          (m:merge($d/*,$delta/*))
                      ) return $d'
                      passing xmldoc, xmltype('<xml><el1>1-UPDATE</el1><el4>4</el4></xml>') as "delta"
                      returning content)
              where id = 1;
              This results in one update and one insert:
              <xml><el1>1-UPDATE</el1><el2>2</el2><el3>3</el3><el4>4</el4></xml>
              It currently produces the following trace:
              *** 2012-03-13 10:13:34.708
              not delete/insert/replace in modify clause
              --------- XQuery NO rewrt expr END-----
              --------- XQuery NO rewrt expr BEG-----
              UDF with noderef
              --------- XQuery NO rewrt expr END-----
              I've noticed that even with a simple XQuery update like the following:
              update myTable set xmldoc = 
                      xmlquery('copy $d := .
                      modify (
                          (replace value of node $d/xml/el1 with "1-UPDATE-2",insert node <el4>4</el4> into $d/xml)
                      ) return $d'
                      passing xmldoc
                      returning content) where id = 1;
              I get the following trace:
              *** 2012-03-13 10:31:42.280
              --------- XQuery NO rewrt expr BEG-----
              non-simple content in target expression
              --------- XQuery NO rewrt expr END-----
              --------- XQuery NO rewrt expr BEG-----
              not delete/insert/replace in modify clause
              --------- XQuery NO rewrt expr END-----
              Non-iterator usage
              Non-iterator usage
              Does this also indicate that a rewrite is not occurring, or is this only when a message like "NO REWRITE Reason ==> xseq:not optuop" is traced?

              I'll try XSLT in the meantime.

              Regards
              Larry
              • 4. Re: XQuery Update (11.2.0.3) and the path table
                odie_63
                Hi Larry,

                What does the explain plan tell you btw?
                Do you see a COLLECTION ITERATOR operation?

                I guess the rewrite can't occur when the modify clause contains different primitives. It's like trying to rewrite the expression so that it can perform an INSERT and an UPDATE at the same time, which sounds like a MERGE but it's probably not that simple.

                Besides, be aware that the actual behaviour of XQuery Update rewrite process is not yet officially documented and for now I can only assume it's driven by the same set of rules than before with updateXML, insertChildXML, etc.
                I've tested it mostly on schema-based (structured) XMLType columns and it seems to works... with a few problems nevertheless : {thread:id=2355054}


                On a side node, I was thinking that maybe XMLDiff/XMLPatch could be an option too, but I don't fully understand how you deal with document order, if at all important?