Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-09-07
4
Medium Priority
?
6,607 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

636 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