Home » Developer & Programmer » JDeveloper, Java & XML » delete a node from xml in oracle (oracle 10.2.0.1)
delete a node from xml in oracle [message #474677] Wed, 08 September 2010 02:29 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Need to delete a node from xml data having many nodes with same name. I have tried by using DELETEXML in the following way.

My test xml is...

with data as (
      select XMLTYPE('<ROWSET>
  <ROW>
    <TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
    </TABLE_T>
   </ROW>
   <ROW>
    <POST_TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
    </POST_TABLE_T>
   </ROW>
  </ROWSET>') val 
     from dual
     )
   select DELETEXML(val,'ROWSET/ROW/POST_TABLE_T') val
   from data



The output is...

<ROWSET>
  <ROW>
    <TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
    </TABLE_T>
  </ROW>
  [color=red][b]<ROW />[/b][/color]
</ROWSET>


I got an extra '<ROW />' node in the output. Shall i need to delete the extra part? If yes...how?
Or any alternative to delete a node from xml.

Regards,
Madhavi.
Re: delete a node from xml in oracle [message #474678 is a reply to message #474677] Wed, 08 September 2010 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Shall i need to delete the extra part?

Only you know if you have to do it. It is your data and only you know what you want to do with them.

Regards
Michel
Re: delete a node from xml in oracle [message #474680 is a reply to message #474678] Wed, 08 September 2010 03:04 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Smile
Finally i'm expecting the output as below.

<ROWSET>
  <ROW>
    <TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
    </TABLE_T>
  </ROW>
  </ROWSET>



How can i get that.
Regards,
Madhavi.
Re: delete a node from xml in oracle [message #474688 is a reply to message #474680] Wed, 08 September 2010 03:46 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You can use:
with data as (
      select XMLTYPE('<ROWSET>
  <ROW>
    <TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
    </TABLE_T>
   </ROW>
   <ROW>
    <POST_TABLE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>1 </VERS_MINOR>
    </POST_TABLE_T>
   </ROW>
  </ROWSET>') val 
     from dual
     )
   select  DELETEXML(val,'ROWSET/ROW[POST_TABLE_T]')
   from data


Re: delete a node from xml in oracle [message #474691 is a reply to message #474688] Wed, 08 September 2010 04:01 Go to previous message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Yes...got it. Thank you Jum.
Previous Topic: reason for java.sql.SQLException: Closed Connection
Next Topic: Issues while retriving data from xmltype
Goto Forum:
  


Current Time: Thu Mar 28 06:16:49 CDT 2024