Comment le simple changement de nœud de la valeur de l'attribut de XMLTYPE dans Oracle 11g r2?

Je veux juste à changer dans ce XML (contenues dans XMLTYPE variable) tous les nœuds nommé "ChildNode" avec "Nom"="B" valeurs d'attribut à "C":

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

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

Merci!

OriginalL'auteur SomeOne | 2010-10-25

oracle plsql

Vous pouvez utiliser updatexml fonction:

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;

OriginalL'auteur andr

Un de plus:

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

OriginalL'auteur Johnbabu Koppolu

Voici une 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;

Pas si simple 🙂

OriginalL'auteur Jokke Heikkilä

-1

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

OriginalL'auteur Cường Đoàn Quốc

Vous devez vous connecter pour publier un commentaire.