how to get DOMTEXT with DBMS_XMLDOM on PL/SQL

Question

i'm trying to get the text value from my xml with DBMS_XMLDOM. For example in:

<Vehicle>
    <Cars name ="Tesla">Model-S</Cars>
</Vehicle>

I want to get "Model-S" as Varchar2. It seems that i can't find any function like getTextNode/getTextValue. What is the proper way to get the value from the DomText?

So far i got :

set serveroutput on size 1000000; 
DECLARE     
  xmlDomElement         xmldom.DOMElement; 
  xmlDomDokument        xmldom.DOMDocument; 
  v_xml    clob; 
BEGIN           
  v_xml := 
   '<Vehicle> 
      <Car> 
        <Tesla>Model-X</Tesla> 
      </Car> 
    </Vehicle>'; 

  xmlDomDokument := dbms_xmldom.newDomDocument(v_xml) ; 
  xmlDomElement :=  dbms_xmldom.getdocumentelement(xmlDomDokument); 
  xmlDomNode :=  dbms_xmldom.makeNode(xmlDomElement);   
  dbms_output.put_line( 'Print node Vehicle : ' || dbms_xmldom.getnodename(xmlDomNode)); 

  -- Question: is there something like "dbms_xmldom.getTextValue"?   

end; 

Thanks in advance.

Regards, Ivan

Answer 1

For getting the value of the text node, you can use XMLDOM.GETNODEVALUE. But to use that, you need to get the first child of the node xmldom.getFirstChild(n). Use the block below to get your output

DECLARE     
  xmlDomElement         xmldom.DOMElement; 
  xmlDomDokument        xmldom.DOMDocument; 
  xmlDomNode            xmldom.DOMNode; 
  lv_domnodelist1       dbms_xmldom.DomNodeList;
  lv_domnodelist2       dbms_xmldom.DomNodeList;
  v_xml    clob; 
BEGIN           
  v_xml := 
   '<Vehicle> 
      <Car> 
        <Tesla>Model-X</Tesla> 
      </Car> 
    </Vehicle>'; 

  xmlDomDokument := dbms_xmldom.newDomDocument(v_xml) ; 
  xmlDomElement :=  dbms_xmldom.getdocumentelement(xmlDomDokument); 
  xmlDomNode :=  dbms_xmldom.makeNode(xmlDomElement);   
  lv_domnodelist1 := DBMS_XMLDOM.GETCHILDNODES(xmlDomNode);

  For i in 0..dbms_xmldom.getLength( lv_domnodelist1 ) - 1 loop
    xmlDomNode := DBMS_XMLDOM.ITEM(lv_domnodelist1,i);
    lv_domnodelist2 := DBMS_XMLDOM.GETCHILDNODES(xmlDomNode);
    FOR J in 0..dbms_xmldom.getLength( lv_domnodelist2 ) - 1 loop
      xmlDomNode := DBMS_XMLDOM.ITEM(lv_domnodelist2,J);
      dbms_output.put_line( 'Print node Name : ' ||  dbms_xmldom.getnodename(xmlDomNode)); 
      xmlDomNode := DBMS_XMLDOM.getFirstChild(xmlDomNode);
      dbms_output.put_line( 'Print node Value : ' || dbms_xmldom.getnodevalue(xmlDomNode)); 
    end loop;
  end loop;
end;