1 2 Previous Next 15 Replies Latest reply: Feb 22, 2013 5:03 AM by Nitesh. RSS

    Need a solution for deleting values in table

    Nitesh.
      Oracle version details

      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



      Table name nit_xml

      Structure

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

      Cust_dtls XML Data's

      <H>
      <cust_dtlss>
      <cust_name>Nitesh</cust_name>
      <cust_phone>1234567890</cust_phone>
      <cust_place>Ambattur</cust_place>
      </cust_dtlss>
      <cust_dtlss>
      <cust_name>Bharathi</cust_name>
      <cust_phone>123</cust_phone>
      <cust_place>Mylapore</cust_place>
      </cust_dtlss>
      </H>


      I want to remove Bharathi alone from this data's and i have tried this way

      DELETE FROM nit_xml a ,
      TABLE (XMLSEQUENCE (EXTRACT (a.cust_dtls, '/H/cust_dtlss'))) b;
      WHERE TRIM(EXTRACTVALUE (value(b), '/H/cust_dtlss/cust_name/text()')) = 'Bharathi';


      but i am unable to make it so can anyone suggest any best way ..

      Rgrds,
      Nitkhush...
        • 1. Re: Need a solution for deleting values in table
          _Karthick_
          Try this
          update nit_xml
             set cust_dtls = deletexml(cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]');
          or if you want to delete the entire node cust_dtlss for cust_name = "Bharathi" you can try
          update nit_xml
             set cust_dtls = deletexml(cust_dtls, '/H/cust_dtlss[cust_name/text()="Bharathi"]');
          • 2. Re: Need a solution for deleting values in table
            Nitesh.
            Hey thanx a lot but what if i want to delete entire that particular row itself means because update cannot be used in that case know and i am not having any other column which could have helped to delete that particular row alone and delete statement cannot help ah for XML as through update only we have to remove data's huh which are present in nodes ..
            • 3. Re: Need a solution for deleting values in table
              Nitesh.
              Sorry i got the solution for deleting data's for entire node also but i am unable to delete that row as it shows oraXML though data's are NULL inside it so how to do that ..
              • 4. Re: Need a solution for deleting values in table
                _Karthick_
                Niteshkhush wrote:
                Sorry i got the solution for deleting data's for entire node also but i am unable to delete that row as it shows oraXML though data's are NULL inside it so how to do that ..
                You can use EXISTSNODE to check if a node exist and then DELETE the entire row.
                delete
                  from nit_xml
                 where existsnode(cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]') = 1
                • 5. Re: Need a solution for deleting values in table
                  Nitesh.
                  No i tried in this way but it didnt work out



                  UPDATE nit_xml
                  SET cust_dtls =
                  DELETEXML (cust_dtls,
                  '/H/cust_dtlss[cust_phone/text()="123"]'
                  )
                  WHERE EXISTSNODE (cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]') =
                  1;


                  Anyways your provided solution is really working but why we are giving value as 1 here i mean the value Bharathi occurs in more than one row and xmlpath is also same but when i am trying to give value as 2 to delete 2 rows at a time then its not working actually why so..

                  DELETE FROM nit_xml
                  WHERE EXISTSNODE (cust_dtls,
                  '/H/cust_dtlss/cust_name[text()="Bharathi"]'
                  ) = 1;

                  Thanks,
                  Nitkhush.
                  • 6. Re: Need a solution for deleting values in table
                    _Karthick_
                    Anyways your provided solution is really working but why we are giving value as 1 here i mean the value Bharathi occurs in more than one row and xmlpath is also same but when i am trying to give value as 2 to delete 2 rows at a time then its not working actually why so..

                    DELETE FROM nit_xml
                    WHERE EXISTSNODE (cust_dtls,
                    '/H/cust_dtlss/cust_name[text()="Bharathi"]'
                    ) = 1;

                    Thanks,
                    Nitkhush.
                    How about reading the fine document on [url http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions048.htm#SQLRF00637]EXISTSNODE. Look at the return value.
                    • 7. Re: Need a solution for deleting values in table
                      Nitesh.
                      Yes karthick I have seen that document already it says as return value is either o or 1 as if exists then it will delete when we give value as 1 and if we give 0 then even though data's exists but it will remove unmatched one's only and i am having same data's in 2 rows and XMLPATH is also same bt its deleting only one row so i just thought as is thr any values available rather than 0 or 1 to delete multiple rows at a same time..

                      Have a look on data's once .. The below mentioned data is available in 2 rows .. But when i tried through update then it didnt update as update just modifies but dont delete XMLtype row fully where as as u provided delete which helped but its deleting single row alone not other duplicates.. I tried also .. Is thr any other way coz 2 rows exists then why its deleting single row alone



                      <H>
                      <cust_dtlss>
                      <cust_name>Nitesh</cust_name>
                      <cust_phone>1234567890</cust_phone>
                      <cust_place>Ambattur</cust_place>
                      </cust_dtlss>
                      <cust_dtlss>
                      <cust_name>Bharathi</cust_name>
                      <cust_phone>123</cust_phone>
                      <cust_place>Mylapore</cust_place>
                      </cust_dtlss>
                      </H>


                      Thanks,
                      Nitesh..

                      Edited by: Niteshkhush on Feb 22, 2013 3:00 PM
                      • 8. Re: Need a solution for deleting values in table
                        _Karthick_
                        Niteshkhush wrote:
                        Yes karthick I have seen that document already it says as return value is either o or 1 as if exists then it will delete when we give value as 1 and if we give 0 then even though data's exists but it will remove unmatched one's only and i am having same data's in 2 rows and XMLPATH is also same bt its deleting only one row so i just thought as is thr any values available rather than 0 or 1 to delete multiple rows at a same time..

                        Have a look on data's once .. The below mentioned data is available in 2 rows .. But when i tried through update then it didnt update as update just modifies but dont delete XMLtype row fully where as as u provided delete which helped but its deleting single row alone not other duplicates.. I tried also .. Is thr any other way coz 2 rows exists then why its deleting single row alone



                        <H>
                        <cust_dtlss>
                        <cust_name>Nitesh</cust_name>
                        <cust_phone>1234567890</cust_phone>
                        <cust_place>Ambattur</cust_place>
                        </cust_dtlss>
                        <cust_dtlss>
                        <cust_name>Bharathi</cust_name>
                        <cust_phone>123</cust_phone>
                        <cust_place>Mylapore</cust_place>
                        </cust_dtlss>
                        </H>


                        Thanks,
                        Nitesh..
                        Let me be clear. Based on your example you have only 1 row in your table nit_xml. This single row has a XMLType which has 2 cust_dtlss node.

                        Your objective is to delete the Row of a table which has a XMLType having cust_name = Bharathi

                        And the DELETE statement does it, It delets the entire row, So whats the problem?
                        • 9. Re: Need a solution for deleting values in table
                          Nitesh.
                          But i have inserted one more row so only i mentioned clearly as same data's exists in 2 rows but its deleting only one row dude .. Its yes at first i shown 1 row only but in my last post i mentioned as duplicate exists so its not working so Is there any other way to do ...
                          • 10. Re: Need a solution for deleting values in table
                            _Karthick_
                            Niteshkhush wrote:
                            But i have inserted one more row so only i mentioned clearly as same data's exists in 2 rows but its deleting only one row dude .. Its yes at first i shown 1 row only but in my last post i mentioned as duplicate exists so its not working so Is there any other way to do ...
                            Works for me
                            SQL> select * from nit_xml;
                             
                            CUST_DTLS
                            --------------------------------------------------------------------------------
                            <H>
                            <cust_dtlss>
                            <cust_name>Nitesh</cust_name>
                            <cust_phone>1234567890</cust_phone>
                            <cust_place>Ambattur</cust_place>
                            </cust_dtlss>
                            <cust_dtlss>
                            <cust_name>Bharathi</cust_name>
                            <cust_phone>123</cust_phone>
                            <cust_place>Mylapore</cust_place>
                            </cust_dtlss>
                            </H>
                             
                            <H>
                            <cust_dtlss>
                            <cust_name>Nitesh</cust_name>
                            <cust_phone>1234567890</cust_phone>
                            <cust_place>Ambattur</cust_place>
                            </cust_dtlss>
                            <cust_dtlss>
                            <cust_name>Bharathi</cust_name>
                            <cust_phone>123</cust_phone>
                            <cust_place>Mylapore</cust_place>
                            </cust_dtlss>
                            </H>
                             
                             
                            SQL> delete
                              2    from nit_xml
                              3   where existsnode(cust_dtls, '/H/cust_dtlss/cust_name[text()="Bharathi"]') = 1
                              4  /
                             
                            2 rows deleted.
                             
                            SQL> select * from nit_xml;
                             
                            no rows selected
                             
                            SQL> 
                            • 11. Re: Need a solution for deleting values in table
                              Nitesh.
                              Hey Sorrie I made some mistake in XMLPATH Sorrie ..

                              Thankss a lot ..

                              Rgds,

                              Nitkhush ...
                              • 12. Re: Need a solution for deleting values in table
                                odie_63
                                EXISTSNODE is deprecated.

                                Use XMLExists instead, as already shown in {message:id=10864233}
                                • 13. Re: Need a solution for deleting values in table
                                  Nitesh.
                                  But XMLEXISTS have shown here some errors when i tried yesterday and i even told u as got some errors .. Even i am unable to understand it clearly as EXISTSNODE i have used already so i have some idea but XMLEXISTS unable to get it and here thrown errors too Odie..

                                  Anyways Thank you...

                                  Rgds,
                                  Nitesh pareek.
                                  • 14. Re: Need a solution for deleting values in table
                                    odie_63
                                    But XMLEXISTS have shown here some errors when i tried yesterday and i even told u as got some errors
                                    You sound like an average end-user complaining that "something" doesn't work "somewhere".
                                    You're a developer, you even say "Software engineer" in your profile. Aren't you able to give what specific errors you had on what statements?

                                    Post the query you've tried with XMLEXISTS, along with the error stack, then I'll be able to show you where you've gone wrong and explain. Thanks.
                                    .. Even i am unable to understand it clearly as EXISTSNODE i have used already so i have some idea but XMLEXISTS unable to get it and here thrown errors too Odie..
                                    Apparently you didn't know any of those two functions till yesterday, so instead of learning how to use EXISTSNODE which again is deprecated, why not learning how to use up-to-date features?
                                    1 2 Previous Next