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

Question

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!

andr · Accepted Answer · 2010-10-25 10:57:16Z

5

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;

answered Oct 25, 2010 at 10:57

andr

1,6468 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Johnbabu Koppolu · Accepted Answer · 2010-10-25 09:13:10Z

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;
/

Jokke Heikkilä · Accepted Answer · 2010-10-25 09:30:50Z

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;

Brian Tompsett - 汤莱恩 · Accepted Answer · 2016-06-19 09:37:06Z

/* 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"><c r="A1" s="2" t="s"><v>0</v></c><c r="B1" s="2" t="s"><v>0</v></c></row>
    <row r="3"><c r="A3" s="2" t="s"><v>1</v></c><c r="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"><c r="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;

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related