XML Manipulation update

[zapster]

Hi,
I have a Clob containing XML string. I need to retrieve that xml and update it. (oracle 8i)

I have access to dbms_xmlQuery and Dbms_xmlSave but this is all new to me so please can you help me do the following:

1) how to read a the clob datatype as XML,
2) how to search for particular node value
3) how to update an attribute in the XML node

Many thanks in advance.

Zapster

 

[zapster]

Hi All,

I have found out that I need to use xmldom but I'm still having problems in changing the value. Ie i cannot get it to save the change value in the original xml.

here is my code

declare                                                                           
                                                                                  
doc xmldom.DOMDocument;                                                           
nl xmldom.DOMNodeList;                                                            
len1 number;                                                                      
len2 number;                                                                      
n xmldom.DOMNode;                                                                 
e xmldom.DOMElement;                                                              
nnm xmldom.DOMNamedNodeMap;                                                       
attrname varchar2(100);                                                           
attrval varchar2(100);                                                            
                                                                                  
v_doc_text varchar2 (2000);                                                       
v_parser      xmlparser.parser;                                                   
t_clob    clob:= empty_clob;                                                      
l_temp varchar2(10);                                                              
l_result varchar2(32000);                                                         
l_Attribute xmldom.DOMAttr;                                                       
                                                                                  
Function printClobOut(result IN CLOB) return varchar2 is                          
xmlstr varchar2(32767);                                                           
line varchar2(32767);                                                             
begin                                                                             
  xmlstr := dbms_lob.SUBSTR(result,32767);                                        
  loop                                                                            
    exit when xmlstr is null;                                                     
    line := substr(xmlstr,1,250-1);--instr(xmlstr,'>')-1);                        
    dbms_output.put_line(line);                                                   
    xmlstr := substr(xmlstr,250+1);--instr(xmlstr,'>')+1);                        
                                                                                  
  end loop;                                                                       
  return 1;                                                                       
end;                                                                              
                                                                                  
                                                                                  
begin                                                                             
                                                                                  
                                                                                  
     select order_xml into t_clob from order_content where order_id = 135280233;  
                                                                                  
     v_parser  :=xmlparser.newParser;                                             
                                                                                  
     xmlparser.PARSECLOB(v_parser, t_clob);                                       
     doc     :=xmlparser.getDocument(v_parser);                                   
                                                                                  
   -- get all elements                                                            
   nl := xmldom.getElementsByTagName(doc, 'basketProduct');                       
   len1 := xmldom.getLength(nl);                                                  
                                                                                  
   -- loop through elements                                                       
   for j in 0..len1-1 loop                                                        
      n := xmldom.item(nl, j);                                                    
      e := xmldom.makeElement(n);                                                 
                                                                                  
      -- get all attributes of element                                            
      nnm := xmldom.getAttributes(n);                                             
                                                                                  
     if (xmldom.isNull(nnm) = FALSE) then                                         
        len2 := xmldom.getLength(nnm);                                            
                                                                                  
        -- loop through attributes                                                
        for i in 0..len2-1 loop                                                   
           n := xmldom.item(nnm, i);                                              
           attrname := xmldom.getNodeName(n);                                     
           IF ((attrname = 'catalogue') or (attrname = 'option')) THEN
                 attrval := xmldom.getNodeValue(n);                               
                 dbms_output.put_line(' ' || attrname || ' = ' || attrval);       
                                                                                  
              if (attrname = 'option') THEN                                 
                                                                                  
                   xmldom.REMOVEATTRIBUTE(e,'option');                      
                                                                                  
                   l_Attribute := xmldom.createAttribute(doc, 'option');    
                                                                                  
                   xmldom.setValue(l_Attribute, 'Y');                             
                                                                                  
                                                                                  
                 l_Attribute:= xmldom.SETATTRIBUTENODE( e, l_Attribute );         
              END IF;                                                             
                                                                                  
           END IF;                                                                
        end loop;                                                                 
        dbms_output.put_line('');                                                 
     end if;                                                                      
   end loop;                                                                      
                                                                                  
   xmldom.writeToClob( doc, t_clob );                                             
                                                                                  
   l_temp := printClobOut(t_clob);                                                
                                                                                  
  xmlparser.freeParser(v_parser);                                                 
  xmldom.freeDocument(doc);  end;

Any ideas on what I need to do to make the xml save the new attribute value. "option=y" ?

Thanks
Zapster