How to simple change node's attribute value of XMLTYPE in Oracle 11g r2?

Question

Hi! I just wanna to change in this XML (contained in XMLTYPE variable) all nodes named "ChildNode" with "Name"="B" attribute values to "C":

<RootNode>
  <ChildNodes>
    <ChildNode Name="A"/>
    <ChildNode Name="B"/>
  </ChildNodes>
</RootNode>

DECLARE
  FXML XMLTYPE;
BEGIN
  FXML := ...; -- see text before
  -- what next?
END;

Thanks!

Answer 1

Here is one solution:

Declare
    xml_nl           DBMS_XMLDOM.DOMNodeList;
    xml_node         DBMS_XMLDOM.DOMNode;
    xml_doc          DBMS_XMLDOM.DOMDocument;
    v_xml_clob       CLOB;
    v_name           VARCHAR2(32767);
    v_xml            VARCHAR2(32767) := 
      '<RootNode>
        <ChildNodes>
          <ChildNode Name="A"/>
          <ChildNode Name="B"/>
        </ChildNodes>
      </RootNode>';
Begin
    xml_doc := DBMS_XMLDOM.NewDOMDocument(XMLType.createXML(v_xml));
    xml_nl  := DBMS_XMLDOM.GetElementsByTagName(xml_doc, 'ChildNode');
    FOR i IN 0 .. (DBMS_XMLDOM.getLength(xml_nl) - 1) LOOP
      xml_node := DBMS_XMLDOM.Item(xml_nl, i);

      DBMS_XSLPROCESSOR.valueOf(xml_node, '@Name', v_name);
      IF v_name IS NOT NULL AND v_name = 'B' THEN
        DBMS_XMLDOM.setAttribute(DBMS_XMLDOM.makeElement(xml_node), 'Name', 'C');
      END IF;
    END LOOP;
    DBMS_LOB.createTemporary(v_xml_clob, cache => FALSE);
    DBMS_LOB.Open(v_xml_clob, DBMS_LOB.lob_readwrite);
    DBMS_XMLDOM.writeToCLob(xml_doc, v_xml_clob);
    DBMS_OUTPUT.put_line(v_xml_clob);
End;

Answer 2

One more:

SET SERVEROUTPUT ON;
DECLARE
  DOC DBMS_XMLDOM.DOMDocument;
  var XMLTYPE := XMLType('<RootNode>  
<ChildNodes>    
<ChildNode Name="A"/>    
<ChildNode Name="B"/>  
</ChildNodes>
</RootNode>');
xmlvalue CLOB;
PROCEDURE changeNameAttributes(
    DOC dbms_xmldom.domdocument)
IS
  nl dbms_xmldom.domnodelist;
  v_clob CLOB;
  LEN NUMBER;
  n dbms_xmldom.domnode;
  nodename  VARCHAR2(4000);
  nodevalue VARCHAR2(4000);
PROCEDURE changeAttributeB(
    n dbms_xmldom.domnode)
IS
  e dbms_xmldom.domelement;
  dn dbms_xmldom.domnode;
  nnm dbms_xmldom.domnamednodemap;
  attrname VARCHAR2(100);
  attrval  VARCHAR2(100);
  LEN      NUMBER;
BEGIN
  e                    := dbms_xmldom.makeelement(n); -- get all attributes of element
  nnm                  := xmldom.getattributes(n);
  IF(xmldom.isnull(nnm) = FALSE) THEN
    LEN                := dbms_xmldom.getlength(nnm); -- loop through attributes
    FOR i              IN 0 .. LEN -1
    LOOP
      dn       := dbms_xmldom.item(nnm, i);
      attrname := dbms_xmldom.getnodename(dn);

      IF(attrname = 'Name' ) THEN
       attrval  := dbms_xmldom.getnodevalue(dn);    
      IF(attrval = 'B') THEN
      dbms_xmldom.setnodevalue(dn,'C');
      END IF;
      END IF;      
    END LOOP;
  END IF;
END changeAttributeB;
BEGIN
  nl    := dbms_xmldom.getelementsbytagname(DOC, '*');
  LEN   := dbms_xmldom.getlength(nl);
  FOR i IN 0 .. LEN -1
  LOOP
    n        := dbms_xmldom.item(nl, i);
    nodename := dbms_xmldom.getnodename(n);
    IF ( nodename = 'ChildNode') THEN   
    changeAttributeB(n);   
    END IF;
  END LOOP;
END changeNameAttributes;
BEGIN
  DOC := DBMS_XMLDOM.newDOMDocument(var); 
  --Before
   DBMS_OUTPUT.PUT_LINE('BEFORE');
    DBMS_LOB.createtemporary (xmlvalue, TRUE);
  DBMS_XMLDOM.writeToClob(DOC, xmlvalue);
  DBMS_OUTPUT.PUT_LINE(xmlvalue); 
  -- Modify
  changeNameAttributes(DOC);  
 -- After
  DBMS_OUTPUT.PUT_LINE('AFTER');
  DBMS_XMLDOM.writeToClob(DOC, xmlvalue);
  DBMS_OUTPUT.PUT_LINE(xmlvalue);
  dbms_xmldom.freedocument(DOC); 
END;
/

Answer 3

You can use updatexml function:

declare
  fOrigXml XmlType := XmlType(
'<RootNode>
  <ChildNodes>
    <ChildNode Name="A"/>
    <ChildNode Name="B"/>
  </ChildNodes>
</RootNode>');
   fResXml XmlType;
begin
  select updatexml((fOrigXml), '/RootNode/ChildNodes/ChildNode[@Name="B"]/@Name', 'C') into fResXml from dual;
end;