Improve company productivity with a Business Account.Sign Up

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

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

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
bilpar
Asked:
bilpar
  • 2
  • 2
1 Solution
 
grim_toasterCommented:
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
 
bilparAuthor Commented:
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
 
grim_toasterCommented:
--> 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
 
bilparAuthor Commented:
I should have said 9.2.0.2 is not available for Windows 2000.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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