Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5314
  • Last Modified:

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>


0
mmoore
Asked:
mmoore
  • 3
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
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;


0
 
mmooreAuthor Commented:
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
0
 
mmooreAuthor Commented:
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?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
slightwv (䄆 Netminder) Commented:
>>this leaves the empty     <SchoolData/> tag, but I can live with that.
I had played with null as well and noticed the empty tag.  As long as you're happy, cool.  I'm still a bit anal about empty tags and didn't want it there.

Everything I post will be on 10g.  There might be quirks between our versions.  You can always try xmldom instead of dbms_xmldom (even in 10g both packages still exist).  I've had some success in the past where a procedure in one of the packages would fail and the same proc from the other would work.

If you can't get getxmltype to work you can use this ugly work-around:
      dbms_lob.createtemporary(org_xml,TRUE);
      dbms_lob.open(org_xml,dbms_lob.lob_readwrite);
      dbms_xmldom.writeToClob(doc, org_xml);
      dbms_lob.close(org_xml);
0
 
mmooreAuthor Commented:
PS updatexml appears to remove the DTD from the xmltype document
0
 
slightwv (䄆 Netminder) Commented:
When I run the PL/SQL stub using the DOM, I don't get the DTD either.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now