4 Replies Latest reply: Jul 23, 2012 4:59 AM by 950269 RSS

    Parameterized UpdateXML

    950269
      Hi,

      I have a xmltype column w/ the following value:
      <blog>
          <blog_post_id>1</blog_post_id>
          <blog_title>xml things</blog_title>
          <comment>
             <comment_id>1</comment_id>
             <comment_text>this is a comment</comment_text>
             <status>approved</status>
          </comment>
          <comment>
             <comment_id>2</comment_id>
             <comment_text>this is the 2nd comment</comment_text>
             <status>approved</status>
          </comment>
          <blog_post_id>2</blog_post_id>
          <blog_title>xml things</blog_title>
          <comment>
             <comment_id>3</comment_id>
             <comment_text>this is 3rd comment</comment_text>
             <status>pending</status>
          </comment>
          <comment>
             <comment_id>4</comment_id>
             <comment_text>this is the 4th  comment</comment_text>
             <status>pending</status>
          </comment>
      
      </blog>
      {code}
      
      I would like to update a comment, say change the status to approved for comment_id = 4
      
      I was doing this:
      
      {code}
      
      UPDATE table SET xml_col = updatexml (xml_col, '/blog/comment/status/text()','approved')
      WHERE XMLEXISTS ('/blog[blog_id=$blog_id]/comment[comment_id=$comment_id]' passing xml_col, 2 as "blog_id", 4 as "comment_id");
      
      {code}
      
      But it updates the entire comments, I was thinking that it should jsut be the one that was filtered in the xmlexists
      
      
      
      Thanks in advance
      
      Edited by: 947266 on Jul 20, 2012 9:56 AM
      corrected text
      
      Edited by: 947266 on Jul 20, 2012 10:04 AM
      
      Removed original query I was confused with xquery, and doesn't seem to work for UPDATEXML
      
      formatted code
      Edited by: 947266 on Sep 10, 2012 9:41 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
        • 1. Re: Parameterized UpdateXML
          odie_63
          Hi,
          But it updates the entire comments, I was thinking that it should jsut be the one that was filtered in the xmlexists
          No, XMLExists() filters rows from your base table just like any other WHERE predicates, not logical records/elements from inside the XML document.
          In this particular case, you're trying to update rows where exist a blog element whose id = 2 and a comment whose id = 4.

          To update a particular node inside the XML instance, you must apply an XPath predicate in updateXML :
          UPDATE my_table 
          SET xml_col = updateXML( xml_col
                                 , '/blog/comment[comment_id="4"]/status/text()'
                                 , 'approved' )
          WHERE XMLExists( '/blog[blog_post_id=$bpid]' 
                           passing xml_col
                                 , 2 as "bpid" )
          ;
          • 2. Re: Parameterized UpdateXML
            950269
            Thanks @odie_63, I was kind of looking for the same syntax for UPDATEXML as I was trying to avoid string concat. Anyways it looks to be inevitable, as I was able to do this:
              declare 
                         v_id varchar2 (5);
              begin
                  v_id := '4';
            
                  UPDATE my_table 
                     SET xml_col = UPDATEXML (xml_col, '/blog/comment[comment_id="' || v_id ||'"]/status/text()', 'approved')
                 WHERE XMLEXists('/blog[blog_post_id=$bpid]' 
                              passing xml_col
                                    , 2 as "bpid");
              end;
            • 3. Re: Parameterized UpdateXML
              odie_63
              Yes, with updateXML and other XML updating functions we have to concat string literals.

              But... What's your db version?
              Starting with version 11.2.0.3, we can use XQuery Update Facility instead and reference bind variables.
              • 4. Re: Parameterized UpdateXML
                950269
                odie_63 wrote:
                Yes, with updateXML and other XML updating functions we have to concat string literals.

                But... What's your db version?
                Starting with version 11.2.0.3, we can use XQuery Update Facility instead and reference bind variables.
                We're using 11.2.0.1.0 Standard, that's the first time I've heard of the XQuery Update facility. Could you give me links / sample?

                Thanks

                Got it, not sure why I miss Marco's post.. [Oracle XMLDB XQuery Update in Database Release 11.2.0.3.0|http://technology.amis.nl/2011/09/22/oracle-xmldb-xquery-update-in-database-release-11-2-0-3-0/]

                Edited by: 947266 on Jul 23, 2012 2:57 AM

                added link

                Edited by: 947266 on Jul 23, 2012 2:58 AM