How to simply change the node value of the XMLTYPE attribute in Oracle 11g r2?

Question

I just want to change this XML (contained in the XMLTYPE variable) to all nodes named "ChildNode" with attributes "Name" = "B" 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

You can use updatexml :

 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; 

Answer 2

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 3

Another:

 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 4

 /* Formatted on 6/19/2016 3:02:05 PM (QP5 v5.126) */ DECLARE var XMLTYPE; doc DBMS_XMLDOM.domdocument; ndoc DBMS_XMLDOM.domnode; docelem DBMS_XMLDOM.domelement; node DBMS_XMLDOM.domnode; childnode DBMS_XMLDOM.domnode; nodelist DBMS_XMLDOM.domnodelist; nodelist2 DBMS_XMLDOM.domnodelist; buf VARCHAR2 (2000); newnode DBMS_XMLDOM.domnode; clonenode DBMS_XMLDOM.domnode; elem DBMS_XMLDOM.domelement; PROCEDURE duyethoiquy (clonenode IN OUT DBMS_XMLDOM.domnode) IS childnode DBMS_XMLDOM.domnode; simpletypechildnodemap DBMS_XMLDOM.domnamednodemap; simpletypeattributenode DBMS_XMLDOM.domnode; BEGIN -- xu ly clonenode nay -- sau do lay may con de duyet tiep -- thay doi node con lev 1.. IF NOT DBMS_XMLDOM.isnull (clonenode) THEN -- xu ly node nay -- thay doi mot vai thuoc tinh cua cay nay simpletypechildnodemap := DBMS_XMLDOM.getattributes (clonenode); simpletypeattributenode := DBMS_XMLDOM.getnameditem (simpletypechildnodemap, 'r'); IF NOT DBMS_XMLDOM.isnull (simpletypeattributenode) THEN DBMS_XMLDOM.setnodevalue (simpletypeattributenode, ''); DBMS_XMLDOM.writetobuffer (simpletypeattributenode, buf); DBMS_OUTPUT.put_line ('attr:' || buf); END IF; IF DBMS_XMLDOM.haschildnodes (clonenode) THEN childnode := DBMS_XMLDOM.getfirstchild (clonenode); --- ghi nhan gia tri WHILE NOT DBMS_XMLDOM.isnull (childnode) LOOP -- xu ly con cua no: duyethoiquy (childnode); childnode := DBMS_XMLDOM.getnextsibling (childnode); ------------------------------------- END LOOP; ELSE -- is leaf -- reset gia tri cua node duoc clone nay roi DBMS_XMLDOM.setnodevalue (clonenode, ''); END IF; END IF; END; BEGIN var := xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"> <sheetData> <row r="1"><cr="A1" s="2" t="s"><v>0</v></c><cr="B1" s="2" t="s"><v>0</v></c></row> <row r="3"><cr="A3" s="2" t="s"><v>1</v></c><cr="B3" s="2" t="s"><v>1</v></c></row> </sheetData> </worksheet>'); -- Create DOMDocument handle: ndoc := DBMS_XMLDOM.makenode(DBMS_XMLDOM.getdocumentelement ( DBMS_XMLDOM.newdomdocument (var))); newnode := DBMS_XMLDOM.makenode(DBMS_XMLDOM.getdocumentelement(DBMS_XMLDOM.newdomdocument(xmltype('<row r="2"><cr="A2" s="1" t="s"><v>0</v></c></row>')))); -- ghi vao node nodelist := DBMS_XSLPROCESSOR.selectnodes (ndoc, '/worksheet/sheetData'); IF NOT DBMS_XMLDOM.isnull (nodelist) THEN node := DBMS_XMLDOM.item (nodelist, 0); childnode := DBMS_XMLDOM.getlastchild (node); clonenode := DBMS_XMLDOM.clonenode (childnode, TRUE); -- thay doi node cha duyethoiquy (clonenode); -- DBMS_XMLDOM.writetobuffer (newnode, buf); -- DBMS_OUTPUT.put_line ('LastChild:' || buf); elem := DBMS_XMLDOM.makeelement(DBMS_XMLDOM.appendchild ( node, DBMS_XMLDOM.makenode(DBMS_XMLDOM.makeelement(DBMS_XMLDOM.importnode ( DBMS_XMLDOM.getownerdocument(node), clonenode, --newnode, TRUE))))); END IF; DBMS_XMLDOM.writetobuffer (ndoc, buf); DBMS_OUTPUT.put_line ('After:' || buf); END; 

- vi du voi xm;