?
Solved

Oracle XMLDOM - How do I update a node text value using dbms_xmldom.setNodeValue()?

Posted on 2004-09-07
4
Medium Priority
?
6,465 Views
Last Modified: 2012-08-14
Hello Experts,

When a node text value is null, how do I update a node text value using dbms_xmldom.setNodeValue() method?  The code below runs in 10g.  The <NAME> node updates.  But, the <NAME2> node does not.  Do i need to add a text node to <NAME2> first?  How?  Is there another way?

declare

  var       XMLType;
  buf       clob;
 
  l_xmldoc  dbms_xmldom.DOMDocument;
  l_root    dbms_xmldom.DOMNode;
  l_node    dbms_xmldom.DOMNode;

begin
   var := xmltype('<PERSON> <NAME>ChangeMe</NAME> <NAME2></NAME2> </PERSON>');

   -- Create DOMDocument handle:
   l_xmldoc     := dbms_xmldom.newDOMDocument(var);
   l_root    := dbms_xmldom.makeNode(l_xmldoc);

   -- before
   dbms_xmldom.writetobuffer(l_root, buf);
   dbms_output.put_line('Before:');
   dbms_output.put_line(buf);
   
   -- replace name.text
   l_node := dbms_xslprocessor.selectSingleNode(l_root,'//NAME/text()');
   if dbms_xmldom.isnull(l_node) then
      dbms_output.put_line('IsNull:');
      dbms_xmldom.setNodeValue(l_node, 'node is null');  
   else
      -- Manipulate:
      dbms_xmldom.setNodeValue(l_node, 'raj');
   end if;
   
   -- replace name2.text... how is this done when the text node is null?  
   l_node := dbms_xslprocessor.selectSingleNode(l_root,'//NAME2/text()');
   if dbms_xmldom.isnull(l_node) then
      dbms_output.put_line('IsNull: And, setNodeValue() does nothing to <NAME2>');
      dbms_xmldom.setNodeValue(l_node, 'node is null');  
   else
      -- Manipulate:
      dbms_xmldom.setNodeValue(l_node, 'node is not null');
   end if;

   -- after
   dbms_xmldom.writetobuffer(l_root, buf);
   dbms_output.put_line('After:');
   dbms_output.put_line(buf);
   
end;


the code above yields the following output:

Before:
<PERSON>
  <NAME>ChangeMe</NAME>
  <NAME2/>
</PERSON>

IsNull: And, setNodeValue() does nothing to <NAME2>
After:
<PERSON>
  <NAME>raj</NAME>
  <NAME2/>
</PERSON>
0
Comment
Question by:bilpar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
grim_toaster earned 2000 total points
ID: 12006384
Unfortunately I do not have 10g, and the code you provided causes problems with my 9i database (maybe I should upgrade!).  Anyway, you would need to create the text node, and here's an example of how to do that based upon your code, but by using just the dbms_xmldom package:

DECLARE
  var     XMLType;
  doc     dbms_xmldom.DOMDocument;
  docelem dbms_xmldom.DOMElement;

  PROCEDURE create_or_replace_text_node(docelem dbms_xmldom.DOMElement, name VARCHAR2, value VARCHAR2) IS
      nodelist  dbms_xmldom.DOMNodelist;
      node      dbms_xmldom.DOMNode;
      childnode dbms_xmldom.DOMNode;
  BEGIN
      nodelist := dbms_xmldom.getElementsByTagName(docelem, name);
      node     := dbms_xmldom.item(nodelist, 0);

      IF (dbms_xmldom.hasChildNodes(node)) THEN
         childnode := dbms_xmldom.getFirstChild(node);
         dbms_xmldom.setNodeValue(childnode, value);
      ELSE
         childnode := dbms_xmldom.makeNode(dbms_xmldom.CREATETEXTNODE(doc, value));
         childnode := dbms_xmldom.insertBefore(node, childnode, childnode);
      END IF;
  END create_or_replace_text_node;
BEGIN
   var := xmltype('<PERSON><NAME>Original Name</NAME><NAME2/></PERSON>');

   doc     := dbms_xmldom.newDOMDocument(var);
   docelem := dbms_xmldom.getDocumentElement(doc);

   -- Extract from the XMLType the before values...
   dbms_output.put_line('Old values...');
   dbms_output.put_line(var.extract('/PERSON/NAME').getStringVal);
   dbms_output.put_line(var.extract('/PERSON/NAME2').getStringVal);

   create_or_replace_text_node(docelem, 'NAME', 'NEW Value for name');
   create_or_replace_text_node(docelem, 'NAME2', 'NEW Name 2 Value');

   -- Extract from the XMLType the after values...
   dbms_output.put_line('NEW VALUES...');
   dbms_output.put_line(var.extract('/PERSON/NAME').getStringVal);
   dbms_output.put_line(var.extract('/PERSON/NAME2').getStringVal);
END;
0
 

Author Comment

by:bilpar
ID: 12010192
This works.  Thanks.  

By the way, I had many problems with 9.2.0.1 and XML.  Apparently, some of the XML DB features are only available with 9.2.0.2 and above.  And, 9.2.0.2 is not downloadable from Oracle without a support agreement.

0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 12013690
--> And, 9.2.0.2 is not downloadable from Oracle without a support agreement.
You can download 9.2.0.2 on XP at the following link (or Linux at 9.2.0.4), however, luckily all of our systems will be upgraded to 9.2.0.5 soon anyway!
http://www.oracle.com/technology/software/products/oracle9i/index.html

Thanks
0
 

Author Comment

by:bilpar
ID: 12015399
I should have said 9.2.0.2 is not available for Windows 2000.  Thanks.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question