1 2 Previous Next 19 Replies Latest reply: Feb 21, 2013 4:09 AM by odie_63 RSS

    Need a solution for updating values in table

    Nitesh.
      My oracle version is 11 and my table name is nit_xml in which column name is cust_dtls

      Data's of nit_xml table


      <root>
      <Cust_dtls> Nitesh </Cust_dtls>
      </root>

      I just want to update Nitesh to Nit ..

      Its i inserted values through this way

      INSERT INTO nit_xml
      VALUES (
      '<root>
      <Cust_dtls> Nitesh </Cust_dtls>
      </root>'

      So i tried updating in my own way as like this way



      UPDATE nit_xml
      SET cust_dtls = '<root>
      <Cust_dtls> Nitesh </Cust_dtls>
      </root>'
      WHERE cust_dtls = '<root>
      <Cust_dtls> Nitesh </Cust_dtls>
      </root>';

      But not working so can anyone give me ideas on XML as how it works and is it possible or not to update through query .. Its note that i want to update column cust_dtls based on same column value and not any other datatype as number or varchar2 because based on other datatype it worked easily and second thing its i am using TOAD application so in that manual updation is possible which is fine but i need to update through query directly ..



      Regards,
      Nitkhush ...
        • 1. Re: Need a solution for updating values in table
          Nitesh.
          Sorry forgot to mention that cust_dtls is xmltype datatype hope you understood but still wanna clear it ..

          Table structure :

          TABLE nit_xml
          Name Null? Type
          ----------------------------------------- -------- ----------------------------
          CUST_DTLS XMLTYPE
          • 2. Re: Need a solution for updating values in table
            Karthick_Arp
            you must use [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions205.htm#SQLRF06172]UPDATEXML
            • 3. Re: Need a solution for updating values in table
              Nitesh.
              But in that link and in that example updation is based on again string as warehouse_name as 'san fransciso' but i want to update based on same column datatype in which i am facing difficulty to write where clause as can u give some hints ..
              • 4. Re: Need a solution for updating values in table
                Karthick_Arp
                Niteshkhush wrote:
                But in that link and in that example updation is based on again string as warehouse_name as 'san fransciso' but i want to update based on same column datatype in which i am facing difficulty to write where clause as can u give some hints ..
                Like this?
                update nit_xml 
                   set cust_dtls = updatexml(cust_dtls, '/root/Cust_dtls/text()', 'Nit')
                 where trim(extract(cust_dtls, '/root/Cust_dtls/text()')) = 'Nitesh'
                • 5. Re: Need a solution for updating values in table
                  Nitesh.
                  Thank you so much Karthick...
                  • 6. Re: Need a solution for updating values in table
                    Nitesh.
                    Sorry but one more question its i am trying to update number but its not working and i tried changing this alone '/root/Cust_dtls/text()' as '/root/Cust_dtls/number' .. I mean how to update numbers then . Its if XML data's like this means

                    <H>
                    <x1>
                    <cust_dtls>Nitesh</cust_dtls>,
                    <cust_dtls>9790755104</cust_dtls>,
                    <cust_dtls>Ambattur</cust_dtls>
                    </x1>
                    <x2>
                    <cust_dtls>Bharathi</cust_dtls>,
                    <cust_dtls>123</cust_dtls>,
                    <cust_dtls>Mylapore</cust_dtls>
                    </x2>
                    </H>

                    I want to change that 979055104 num to some number so i tried here but its not working again and i even tried changing that Ambattur text to other text but that also not working as showing 0 rows updated .. Can We update that part of text alone .. Give me any link in which all DML's on XML are available or else if you have some time means try to tell a bit ..

                    Thanks,
                    Nitkhush..
                    • 7. Re: Need a solution for updating values in table
                      Karthick_Arp
                      Your xml tag seems flawed
                      <H>
                      <x1>
                      <cust_dtls>Nitesh</cust_dtls>,
                      <cust_dtls>9790755104</cust_dtls>,
                      <cust_dtls>Ambattur</cust_dtls>
                      </x1>
                      <x2>
                      <cust_dtls>Bharathi</cust_dtls>,
                      <cust_dtls>123</cust_dtls>,
                      <cust_dtls>Mylapore</cust_dtls>
                      </x2>
                      </H>
                      You have same tag name cust_dtls for all the three elements, why is that? And what about the Comma(,) after the tags?
                      • 8. Re: Need a solution for updating values in table
                        Nitesh.
                        Okay I removed comma's sorry i didnt notice that and also i changed tags as

                        <H>
                        <x1>
                        <cust_name>Nitesh</cust_name>
                        <cust_phone>9790755104</cust_phone>
                        <cust_place>Ambattur</cust_place>
                        </x1>
                        <x2>
                        <cust_name>Bharathi</cust_name>
                        <cust_phone>123</cust_phone>
                        <cust_place>Mylapore</cust_place>
                        </x2>
                        </H>

                        and i tried updating this way but still not working..

                        UPDATE nit_xml
                        SET cust_dtls = UPDATEXML (cust_dtls, '/root/Cust_phone/text()', 132345)
                        WHERE TRIM (EXTRACT (cust_dtls, '/root/Cust_phone/text()')) = 9790755104;
                        • 9. Re: Need a solution for updating values in table
                          Karthick_Arp
                          Niteshkhush wrote:
                          Okay I removed comma's sorry i didnt notice that and also i changed tags as

                          <H>
                          <x1>
                          <cust_name>Nitesh</cust_name>
                          <cust_phone>9790755104</cust_phone>
                          <cust_place>Ambattur</cust_place>
                          </x1>
                          <x2>
                          <cust_name>Bharathi</cust_name>
                          <cust_phone>123</cust_phone>
                          <cust_place>Mylapore</cust_place>
                          </x2>
                          </H>

                          and i tried updating this way but still not working..

                          UPDATE nit_xml
                          SET cust_dtls = UPDATEXML (cust_dtls, '/root/Cust_phone/text()', 132345)
                          WHERE TRIM (EXTRACT (cust_dtls, '/root/Cust_phone/text()')) = 9790755104;
                          XML Tags are case sensitive. Your tag is "cust_phone" In your update you have used "Cust_phone"
                          • 10. Re: Need a solution for updating values in table
                            odie_63
                            Hi,

                            If you want help, start by giving us relevant information such as full database version, DDL for the table and INSERT statement to reproduce the case.
                            Most importantly, provide a valid XML document close enough to your real structure.
                            • 11. Re: Need a solution for updating values in table
                              odie_63
                              and i tried updating this way but still not working..

                              UPDATE nit_xml
                              SET cust_dtls = UPDATEXML (cust_dtls, '/root/Cust_phone/text()', 132345)
                              WHERE TRIM (EXTRACT (cust_dtls, '/root/Cust_phone/text()')) = 9790755104;
                              There's no <root> or <Cust_phone> elements in the XML.
                              And the WHERE clause is not correct either.
                              • 12. Re: Need a solution for updating values in table
                                Nitesh.
                                My oracle version is 11 and my table name is nit_xml in which column name is cust_dtls

                                TABLE nit_xml
                                Name Null? Type
                                ----------------------------------------- -------- ----------------------------
                                CUST_DTLS XMLTYPE

                                This is new data I have inserted newly now ..

                                <H>
                                <cust_dtls>
                                <cust_name>Nitesh</cust_name>
                                <cust_phone>9790755104</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>

                                Update query :

                                UPDATE nit_xml
                                SET cust_dtls = UPDATEXML (cust_dtls, '/root/cust_phone/text()', 132345)
                                WHERE TRIM (EXTRACT (cust_dtls, '/root/cust_phone/text()')) = 9790755104;

                                Its showing as 0 rows updated i cant find out any reason as what's happening .. I want to know also as why without TRIM its extraction is not possible ...
                                • 13. Re: Need a solution for updating values in table
                                  odie_63
                                  Niteshkhush wrote:
                                  My oracle version is 11
                                  No.

                                  Full version number please :
                                  SELECT * FROM v$version;
                                  Its showing as 0 rows updated i cant find out any reason as what's happening ..
                                  I still don't see any tag named "root" in the document, do you?
                                  • 14. Re: Need a solution for updating values in table
                                    Karthick_Arp
                                    Niteshkhush wrote:
                                    My oracle version is 11 and my table name is nit_xml in which column name is cust_dtls

                                    TABLE nit_xml
                                    Name Null? Type
                                    ----------------------------------------- -------- ----------------------------
                                    CUST_DTLS XMLTYPE

                                    This is new data I have inserted newly now ..

                                    <H>
                                    <cust_dtls>
                                    <cust_name>Nitesh</cust_name>
                                    <cust_phone>9790755104</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>

                                    Update query :

                                    UPDATE nit_xml
                                    SET cust_dtls = UPDATEXML (cust_dtls, '/root/cust_phone/text()', 132345)
                                    WHERE TRIM (EXTRACT (cust_dtls, '/root/cust_phone/text()')) = 9790755104;

                                    Its showing as 0 rows updated i cant find out any reason as what's happening .. I want to know also as why without TRIM its extraction is not possible ...
                                    Your XPath is incorrect. it should be /H/cust_dtls/cust_phone/text(). You dont have a tag as root. Its H.
                                    1 2 Previous Next