Get xml element value in Oracle PL SQL

Question

Does anyone know how to get the values ​​of <ZIPCODE> and <CITY> using PL / SQL? I followed the tutorial over the net, however it can extract element names, but not their meanings. Do any of you know what the problem is? I have already consulted with Google (the Internet, which was kept secret) about this, but with no luck :(

 <Zipcodes> <mappings Record="4"> <STATE_ABBREVIATION>CA</STATE_ABBREVIATION> <ZIPCODE>94301</ZIPCODE> <CITY>Palo Alto</CITY> </mappings> </Zipcodes> 

here is an example code:

 -- prints elements in a document PROCEDURE printElements(doc DBMS_XMLDOM.DOMDocument) IS nl DBMS_XMLDOM.DOMNodeList; n DBMS_XMLDOM.DOMNode; len number; BEGIN -- get all elements nl := DBMS_XMLDOM.getElementsByTagName(doc, '*'); len := DBMS_XMLDOM.getLength(nl); -- loop through elements FOR i IN 0 .. len - 1 LOOP n := DBMS_XMLDOM.item(nl, i); testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(n); DBMS_OUTPUT.PUT_LINE (testr); END LOOP; DBMS_OUTPUT.PUT_LINE (''); END printElements; 

Answer 1

You need to change the line

 testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(n); 

to

 testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(n)); 

In the XML DOM, elements have no meaning to talk about. Element nodes contain text nodes as child elements, and it is these nodes that contain the desired values.

EDIT (in response to Tomalak's comment): I don't know any functions in DBMS_XMLDOM to get the combined value of all child text nodes of an element. If you need it, then you may need to use something like the following function:

 CREATE OR REPLACE FUNCTION f_get_text_content ( p_node DBMS_XMLDOM.DOMNode ) RETURN VARCHAR2 AS l_children DBMS_XMLDOM.DOMNodeList; l_child DBMS_XMLDOM.DOMNode; l_text_content VARCHAR2(32767); l_length INTEGER; BEGIN l_children := DBMS_XMLDOM.GetChildNodes(p_node); l_length := DBMS_XMLDOM.GetLength(l_children); FOR i IN 0 .. l_length - 1 LOOP l_child := DBMS_XMLDOM.Item(l_children, i); IF DBMS_XMLDOM.GetNodeType(l_child) IN (DBMS_XMLDOM.TEXT_NODE, DBMS_XMLDOM.CDATA_SECTION_NODE) THEN l_text_content := l_text_content || DBMS_XMLDOM.GetNodeValue(l_child); END IF; END LOOP; RETURN l_text_content; END f_get_text_content; / 

Answer 2

This is a simple illustration of how to extract the desired values ​​from a document:

 declare vDOM dbms_xmldom.DOMDocument; vNodes dbms_xmldom.DOMNodeList; vXML xmltype := xmltype('<Zipcodes> <mappings Record="4"> <STATE_ABBREVIATION>CA</STATE_ABBREVIATION> <ZIPCODE>94301</ZIPCODE> <CITY>Palo Alto</CITY> </mappings> </Zipcodes>'); begin -- create the dom document from our example xmltype vDOM := dbms_xmldom.newDOMDocument(vXML); -- find all text nodes in the dom document and return them into a node list vNodes := dbms_xslprocessor.selectNodes (n => dbms_xmldom.makeNode(dbms_xmldom.getDocumentElement(vDOM)) ,pattern => '//*[self::ZIPCODE or self::CITY]/text()' ,namespace => null ); -- iterate through the node list for i in 0 .. dbms_xmldom.getlength(vNodes) - 1 loop -- output the text value of each text node in the list dbms_output.put_line(dbms_xmldom.getNodeValue(dbms_xmldom.item(vNodes,i))); end loop; -- free up document resources dbms_xmldom.freeDocument(vDOM); end; 

The above results are requested:

 94301 Palo Alto 

Replacing the xpath template in the above example with the template => '// text ()' leads to the conclusion:

 CA 94301 Palo Alto 

T. All text in the document. Of course, many variations of this topic can be used with this technique.