1 2 Previous Next 19 Replies Latest reply: Feb 21, 2013 4:09 AM by odie_63 Go to original post RSS
      • 15. Re: Need a solution for updating values in table
        Nitesh.
        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
        PL/SQL Release 11.2.0.3.0 - Production
        CORE     11.2.0.3.0     Production
        TNS for Linux: Version 11.2.0.3.0 - Production
        NLSRTL Version 11.2.0.3.0 - Production


        No No now no root clause i removed so my new data's are real so please now we will follow that and go ahead ..
        • 16. Re: Need a solution for updating values in table
          odie_63
          OK, now we're going somewhere.

          This is the correct way to filter and update :
          SQL> create table nit_xml (cust_dtls xmltype);
           
          Table created
           
          SQL> 
          SQL> insert into nit_xml values (
            2   xmltype('<H>
            3  <cust_dtls>
            4  <cust_name>Nitesh</cust_name>
            5  <cust_phone>9790755104</cust_phone>
            6  <cust_place>Ambattur</cust_place>
            7  </cust_dtls>
            8  <x2>
            9  <cust_name>Bharathi</cust_name>
           10  <cust_phone>123</cust_phone>
           11  <cust_place>Mylapore</cust_place>
           12  </x2>
           13  </H>')
           14  );
           
          1 row inserted
           
          SQL> 
          SQL> update nit_xml
            2  set cust_dtls =
            3      updatexml(
            4        cust_dtls
            5      , '/H/cust_dtls/cust_phone/text()'
            6      , '1234567890'
            7      )
            8  where xmlexists(
            9          '/H/cust_dtls[cust_phone=$num]'
           10          passing cust_dtls
           11                , '9790755104' as "num"
           12        ) ;
           
          1 row updated
           
          SQL> set long 5000
          SQL> 
          SQL> select xmlserialize(document cust_dtls) as updated_doc
            2  from nit_xml;
           
          UPDATED_DOC
          --------------------------------------------------------------------------------
          <H>
            <cust_dtls>
              <cust_name>Nitesh</cust_name>
              <cust_phone>1234567890</cust_phone>
              <cust_place>Ambattur</cust_place>
            </cust_dtls>
            <x2>
              <cust_name>Bharathi</cust_name>
              <cust_phone>123</cust_phone>
              <cust_place>Mylapore</cust_place>
            </x2>
          </H>
           
          And since you're on 11.2.0.3, as updateXML is deprecated, you can start using XQuery Update instead :
          update nit_xml
          set cust_dtls = 
              xmlquery(
                'copy $d := .
                 modify ( 
                   replace value of node $d/H/cust_dtls/cust_phone with $num 
                 )
                 return $d'
                passing cust_dtls
                      , '1234567890' as "num"
                returning content
              )
          where xmlexists(
                  '/H/cust_dtls[cust_phone=$num]' 
                  passing cust_dtls
                        , '9790755104' as "num" 
                ) ;
          Edited by: odie_63 on 21 févr. 2013 10:37 - added XQU example
          • 17. Re: Need a solution for updating values in table
            Nitesh.
            Thanks Odie still i got some errors but i managed and i just have one doubt its like text() is for strings and is there any other function available for numbers alone .. As however data's stored inside XMLDATATYPE column is considered as string only but still just asking ..

            Edited by: Niteshkhush on Feb 21, 2013 3:24 PM
            • 18. Re: Need a solution for updating values in table
              Nitesh.
              And Odie its XQuery Update is very very complicated as i am unable to understand it clearly anyways Thank you so much for your valuable time given to me ..

              Regards,
              Niteshkhush
              • 19. Re: Need a solution for updating values in table
                odie_63
                Niteshkhush wrote:
                Thanks Odie still i got some errors but i managed and i just have one doubt its like text() is for strings and is there any other function available for numbers alone
                "text()" is not a function. It's an XPath node test.
                Simply put, a text node is character data enclosed by element tags, e.g.
                <mytag>some value</mytag>
                "mytag" is an element node, "some value" is a text node.
                The actual datatype of the text node is not relevant at this point, that concept is addressed by XML schemas.

                As however data's stored inside XMLDATATYPE column is considered as string only but still just asking ..
                No, XMLType is not a string, it's an XMLType.
                It used to be stored internally in a CLOB prior to 11.2.0.2, now the defaut storage is Binary XML.
                1 2 Previous Next