Solved

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

Posted on 2004-09-07
4
6,211 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 500 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
oracle collections 2 29
Oracle cursor lifecycle inside procedure. 2 29
UTL_FILE invalid file operation 5 46
Migration from SQL server to oracle (XML input) 4 55
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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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