Retrieve value of an xml element in Oracle PL SQL

Question

Does anybody know how to retrieve the values of <ZIPCODE> and <CITY> using PL/SQL? I have followed a tutorial over the net, however, it can retrieve the element names, but not their values. Any of you know what seems to be the problem? I have already consulted Google (the internet's well kept secret) over this but no luck :(

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

here's the sample 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;

Personally I prefer to use XMLType and use the Extract function to get them via XPath. e.g. `myxml.Extract('/Zipcodes/mappings/ZIPCODE/text()');` - must simpler than walking the DOM. — Jeffrey Kemp, Feb 08 '12 at 06:22

Luke Woodward · Answer 1 · 2012-02-07T21:12:37.613

12

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 XML DOM, elements don't have any 'value' to speak of. Element nodes contain Text nodes as children, and it is these nodes that contain the values you want.

EDIT (in response to Tomalak's comment): I'm not aware of any functions in DBMS_XMLDOM to obtain the combined value of all the child text nodes of an element. If that's what you need, then you may well 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;
/

edited Feb 07 '12 at 21:12

answered Apr 13 '09 at 09:17

Luke Woodward

63,336
16
89
104

What if a node contains multiple descendants - how do you obtain their combined text? – Tomalak Apr 13 '09 at 09:30
2

Thanks for taking the time, I gave my +1 earlier already. However - shouldn't this be recursive in some way? Maybe XPath is a more appropriate choice? (I admittedly don't know much about Oracle, so I have no idea what's required to do XPath queries.) – Tomalak Apr 13 '09 at 11:55
The above function can easily be made recursive by adding an ELSIF clause to the IF block. Whether this is worth doing depends on what the OP needs. XPath is possible, however colleagues of mine have had reliability issues with Oracle XML DB (especially with XSLT) so I'd rather not go that way. – Luke Woodward Apr 13 '09 at 15:22
What about DBMS_XMLDOM.CDATA_SECTION_NODE? – xmedeko Feb 07 '12 at 14:41
There is also a function dbms_xslprocessor.valueOf(...), however, in Oracle 9i it does not handle XPath queries well. E.g. XPath expressions '/a//text()' or 'string(/a)' does not work as they should. (Note the XPath 'string(/a)' should do the same trick as f_get_text_content above. – xmedeko Feb 08 '12 at 07:59
@Tomalak - please see my answer below in order to pull text recursively from the whole document, or from any desired portion of the document – Pancho Oct 13 '16 at 15:15

Pancho · Answer 2 · 2016-10-14T20:47:42.030

This is a simple illustration of how to retrieve the desired values from the 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 in the output requested:

94301
Palo Alto

Replacing the xpath pattern in the above example with pattern => '//text()' results in the output:

CA
94301
Palo Alto

ie. all the text in the document. Many variations on this theme are of course possible using this technique.

Retrieve value of an xml element in Oracle PL SQL

2 Answers2

Linked