CODEWITHSUNDEEP
sqlxmloracle-databaseplsql
Godcode
Godcode

Reputation: 141

Retrieve value of an xml element in Oracle PL SQL

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;

Upvotes: 6

Views: 35818

Answers (2)

Pancho
Pancho

Reputation: 2193

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.

Upvotes: 0

Luke Woodward
Luke Woodward

Reputation: 64959

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

Upvotes: 12

Related Questions