[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6717
  • 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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