Home » Developer & Programmer » JDeveloper, Java & XML » How can i delete a node from a pl/sql variable of xmltype (oracle 10g)
How can i delete a node from a pl/sql variable of xmltype [message #459988] Wed, 09 June 2010 05:16 Go to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
Hi,
Please tell me how to delete a node from a variable of xmltype.
I have below data in one of my pl/sql variable of xmltype datatype. I want to delete the complete node of "FILESPEC".

<ROWSET>
  <ROW>
    <TABLESPACE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>0 </VERS_MINOR>
      <TS_NUM>73</TS_NUM>
      <NAME>S_ACT_TEG_ERR_I</NAME>
      <OWNER_NUM>0</OWNER_NUM>
  <FILESPEC>
        <FILESPEC_ITEM>
        <TS_NUM>73</TS_NUM>
        </FILESPEC_ITEM>
      </FILESPEC>
    </TABLESPACE_T>
  </ROW>
</ROWSET>

REGARDS,
Madhavi.
Re: How can i delete a node from a pl/sql variable of xmltype [message #460013 is a reply to message #459988] Wed, 09 June 2010 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select '<ROWSET>
  3    <ROW>
  4      <TABLESPACE_T>
  5        <VERS_MAJOR>1</VERS_MAJOR>
  6        <VERS_MINOR>0 </VERS_MINOR>
  7        <TS_NUM>73</TS_NUM>
  8        <NAME>S_ACT_TEG_ERR_I</NAME>
  9        <OWNER_NUM>0</OWNER_NUM>
 10    <FILESPEC>
 11          <FILESPEC_ITEM>
 12          <TS_NUM>73</TS_NUM>
 13          </FILESPEC_ITEM>
 14        </FILESPEC>
 15      </TABLESPACE_T>
 16    </ROW>
 17  </ROWSET>' val 
 18    from dual
 19    )
 20  select regexp_replace(val,'<FILESPEC>.*</FILESPEC>','',1,1,'n') val
 21  from data
 22  /
VAL
-----------------------------------------------------------------------------
<ROWSET>
  <ROW>
    <TABLESPACE_T>
      <VERS_MAJOR>1</VERS_MAJOR>
      <VERS_MINOR>0 </VERS_MINOR>
      <TS_NUM>73</TS_NUM>
      <NAME>S_ACT_TEG_ERR_I</NAME>
      <OWNER_NUM>0</OWNER_NUM>

    </TABLESPACE_T>
  </ROW>
</ROWSET>

Regards
Michel
Re: How can i delete a node from a pl/sql variable of xmltype [message #460024 is a reply to message #460013] Wed, 09 June 2010 06:50 Go to previous messageGo to next message
madhavi babburi
Messages: 117
Registered: May 2009
Location: Hyderabad
Senior Member
hi,
In pl/sql, how can we replace? I used the select statement what you provided me. I got below error.
CREATE OR REPLACE FUNCTION DDI_TEST.DBLINK_XML
RETURN XMLTYPE
AUTHID CURRENT_USER
AS
v_meta_handle NUMBER;
v_meta_handle_trans NUMBER;
V_DOC XMLTYPE;
V_Next_Doc XMLTYPE;
V_LOB XMLTYPE;
VAL XMLTYPE;
BEGIN
-- Specify the object type.
  v_meta_handle := DBMS_METADATA.OPEN('DATABASE_EXPORT');
  DBMS_METADATA.SET_FILTER(v_meta_handle, 'INCLUDE_PATH_EXPR','IN''TABLESPACE''');
 DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''SYS''','SCHEMA');
 DBMS_METADATA.SET_FILTER(v_meta_handle, 'NAME_EXPR','IN''S_ACT_TEG_ERR_I''','TABLESPACE');
 LOOP
    v_doc   := DBMS_METADATA.FETCH_XML(v_meta_handle);
    EXIT WHEN v_doc IS NULL;
    IF V_Lob is null then 
     v_lob   := v_doc;
    ELSE
    SELECT EXTRACT(V_Doc, '/ROWSET/ROW') INTO V_Next_Doc FROM DUAL;
    SELECT APPENDCHILDXML(V_Lob,'/ROWSET',V_Next_Doc) INTO V_Lob FROM DUAL;
    END IF;
 END LOOP;
  
   select regexp_replace(V_LOB,'<FILESPEC>.*</FILESPEC>','',1,1,'n') INTO VAL
   from DUAL;
  return VAL;
   end;


PLS-00801: internal error [*** ASSERT at file pdw4.c, line  796; Cannot coerce between type 31 and type 49; 
DBLINK_XML__DDI_TEST__F__233179[28, 4]]


How to solve the above issue?
Regards,
Madhavi.

[Updated on: Wed, 09 June 2010 06:53] by Moderator

Report message to a moderator

Re: How can i delete a node from a pl/sql variable of xmltype [message #460030 is a reply to message #460024] Wed, 09 June 2010 06:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to solve the above issue?

I don't know but "internal error" sounds to me as "call oracle".

Quote:
I used the select statement what you provided me

Use directly in PL/SQL:
SQL> declare 
  2    v_lob varchar2(30000);
  3    val varchar2(30000);
  4  begin
  5    v_lob := '<ROWSET>
  6    <ROW>
  7      <TABLESPACE_T>
  8        <VERS_MAJOR>1</VERS_MAJOR>
  9        <VERS_MINOR>0 </VERS_MINOR>
 10        <TS_NUM>73</TS_NUM>
 11        <NAME>S_ACT_TEG_ERR_I</NAME>
 12        <OWNER_NUM>0</OWNER_NUM>
 13    <FILESPEC>
 14          <FILESPEC_ITEM>
 15          <TS_NUM>73</TS_NUM>
 16          </FILESPEC_ITEM>
 17        </FILESPEC>
 18      </TABLESPACE_T>
 19    </ROW>
 20  </ROWSET>';
 21    val := regexp_replace(V_LOB,'<FILESPEC>.*</FILESPEC>','',1,1,'n');
 22  end;
 23  /

PL/SQL procedure successfully completed.

What is your Oracle version with 4 decimals (always post this information).

Regards
Michel
Previous Topic: need query to extract values from xml data (merged)
Next Topic: select current node threw xmlquery
Goto Forum:
  


Current Time: Thu Mar 28 06:53:22 CDT 2024