asked on

DBMS_XMLDOM REMOVECHILD PL/SQL XML DOM

I am trying to delete the xml node where the schoolkey = 10001. It is not working.
What am I doing wrong?

In other words I want ...
<SchoolData>
<SchoolKey>10001</SchoolKey>
<ProgramName>BA in Narwhal Culing</ProgramName>
</SchoolData>
to be removed.

var res clob;
set autoprint on
/
declare
xml XMLTYPE;
v_item PLS_INTEGER;
v_doc DBMS_XMLDOM.domdocument;
v_ndoc DBMS_XMLDOM.domnode;
v_nlist DBMS_XMLDOM.domnodelist;
v_node DBMS_XMLDOM.domnode;
v_ntext DBMS_XMLDOM.domnode;
v_tag VARCHAR2 (30) := 'SchoolKey';
v_delval VARCHAR2 (5) := '10001';
v_del DBMS_XMLDOM.domnode;
v_str_val VARCHAR2 (30);
BEGIN
SELECT XMLTYPE.createxml (x)
INTO xml
FROM (SELECT '<?xml version="1.0"?>
<!DOCTYPE splitload [
<!ELEMENT splitload (SchoolList+)>
<!ATTLIST SchoolList PortalName CDATA >
<!ELEMENT SchoolList (SchoolData+)>
<!ELEMENT SchoolData (SchoolKey,ProgramName)>
<!ELEMENT SchoolKey (#PCDATA)>
<!ELEMENT ProgramName (#PCDATA)>
]>
<splitload>
<SchoolList PortalName = "EDUCATION">
<SchoolData>
<SchoolKey>10000</SchoolKey>
<ProgramName>Masters in Cooking</ProgramName>
</SchoolData>
<SchoolData>
<SchoolKey>10001</SchoolKey>
<ProgramName>BA in Narwhal Culing</ProgramName>
</SchoolData>
<SchoolData>
<SchoolKey>10002</SchoolKey>
<ProgramName>Certificate in Certificate Design</ProgramName>
</SchoolData>
</SchoolList>
</splitload>' x
FROM DUAL);
v_doc := DBMS_XMLDOM.newdomdocument (xml);
v_ndoc := DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (v_doc));
v_nlist := DBMS_XMLDOM.getelementsbytagname (v_doc, v_tag);
FOR v_item IN 0 .. DBMS_XMLDOM.getlength (v_nlist) - 1
LOOP
v_node := DBMS_XMLDOM.item (v_nlist, v_item);
v_ntext := DBMS_XMLDOM.getfirstchild (v_node);
v_str_val := DBMS_XMLDOM.getnodevalue (v_ntext);
DBMS_OUTPUT.put_line (v_str_val);
IF v_str_val = v_delval
THEN
v_del :=
DBMS_XMLDOM.removechild (DBMS_XMLDOM.getparentnode (v_node),
v_node
);
END IF;
END LOOP;
SELECT xml.getClobVal() INTO :res FROM dual;
END;
/

here are the results incase you don't have access to sqlplus
--------------------------------------------------------------------------------------------
PL/SQL procedure successfully completed.

RES
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE splitload [
<!ELEMENT
splitload (SchoolList+)>
<!ATTLIST SchoolList PortalName CDATA >

<!ELEMENT SchoolList (SchoolData+)>
<!ELEMENT SchoolData
(SchoolKey,ProgramName)>
<!ELEMENT SchoolKey (#PCDATA)>

<!ELEMENT ProgramName (#PCDATA)>
]>
<splitload>

<SchoolList PortalName = "EDUCATION">
<SchoolData>

<SchoolKey>10000</SchoolKey>
<ProgramName>Masters in Cooking<
/ProgramName>
</SchoolData>
<SchoolData>
<
SchoolKey>10001</SchoolKey>
<ProgramName>BA in Narwhal Culing</Pro
gramName>
</SchoolData>
<SchoolData>
<Scho
olKey>10002</SchoolKey>
<ProgramName>Certificate in Certificate De
sign</ProgramName>
</SchoolData>
</SchoolList>
</split
load>

Elapsed: 00:00:00.02
SQL>
---------------------------------------------------------------------------
as you can see, it is still there <argh>

slightwv (䄆 Netminder)

Check out your last line. You've converted the xmltype variable 'XML' into a domdocument, performed all that processing and then select the xmltype 'XML' into the clob and ignore the modified domdocument.

Give this a try.
Change:
SELECT xml.getClobVal() INTO :res FROM dual;
to
:res := dbms_xmldom.getxmltype(v_doc).getclobval();

All that said and not knowing your specific requirements I'm thinking there might be easiser ways of doing this without using PL/SQL and the DOM. For example:

select updatexml(xmltype('<?xml version="1.0"?>
<!DOCTYPE splitload [
<!ELEMENT splitload (SchoolList+)>
<!ATTLIST SchoolList PortalName CDATA >
<!ELEMENT SchoolList (SchoolData+)>
<!ELEMENT SchoolData (SchoolKey,ProgramName)>
<!ELEMENT SchoolKey (#PCDATA)>
<!ELEMENT ProgramName (#PCDATA)>
]>
<splitload>
<SchoolList PortalName = "EDUCATION">
<SchoolData>
<SchoolKey>10000</SchoolKey>
<ProgramName>Masters in Cooking</ProgramName>
</SchoolData>
<SchoolData>
<SchoolKey>10001</SchoolKey>
<ProgramName>BA in Narwhal Culing</ProgramName>
</SchoolData>
<SchoolData>
<SchoolKey>10002</SchoolKey>
<ProgramName>Certificate in Certificate Design</ProgramName>
</SchoolData>
</SchoolList>
</splitload>'),'//splitload/SchoolList/SchoolData[SchoolKey=10001]',
'//'
).getclobval() x
FROM DUAL;

mmoore

ASKER

I have not checked out the first part of your answer yet, but the second part is good with one little correction. The '//' part gives :
25 </splitload>'),'//splitload/SchoolList/SchoolData[SchoolKey=10001]',
26 '//'
27 ).getclobval() x
28 FROM DUAL;
ERROR:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '/'
Error at line 1
----------------------------------
after I replace '//' with null, the result is :
25 </splitload>'),'//splitload/SchoolList/SchoolData[SchoolKey=10001]',
26 null
27 ).getclobval() x
28* FROM DUAL
SQL> /

X
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<splitload>
<SchoolList PortalName="EDUCATION">
<SchoolData>
<SchoolKey>10000</SchoolKey>
<ProgramName>Masters in Cooking</ProgramName>
</SchoolData>
<SchoolData/>
<SchoolData>
<SchoolKey>10002</SchoolKey>
<ProgramName>Certificate in Certificate Design</ProgramName>
</SchoolData>
</SchoolList>
</splitload>
----------------------------------------------------------
this leaves the empty <SchoolData/> tag, but I can live with that.

Regarding your comment " You've converted the xmltype variable 'XML' into a domdocument, performed all that processing and then select the xmltype 'XML' into the clob and ignore the modified domdocument."

I was following the example on page 213 of Oracle Database 10g XML & SQL: Design, Build & Manage XML Applications in Java,C, C++ & PL/SQL. The example seems to show that the DOM objects are just interfaces attached to the XMLTYPE variable. They instanciate The DOM object from the XML type and then alter the DOM objects with SETNODEVALUE. They never cast the DOM object back into an XMLtype, yet when they display the XML object, the changes seem to have been made to it. Weird.

Anyway I want to play around with your first solution and leave the question open a bit longer. After that I'll reward you the points.

Regards,
Mike

mmoore

ASKER

On OTN I found that there is a bug in GETXMLTYPE in 9.2 (I am using 9.2)

see http://forums.oracle.com/forums/thread.jsp?forum=157&thread=278970&tstart=30&trange=15
Even the 9.2 documents shows that method with NO input parameters.

Ideas?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmoore

ASKER

PS updatexml appears to remove the DTD from the xmltype document

slightwv (䄆 Netminder)

When I run the PL/SQL stub using the DOM, I don't get the DTD either.