One question that comes up with some frequency on various OTN forums is "How do I parse this XML?" Depending upon where the XML is (PL/SQL or DB) and what version of Oracle, there are different options that can be used. In starting simple, I show some examples for parsing some basic XML via PL/SQL. This example shows some basic ways to parse XML.
DECLARE
l_doc VARCHAR2(2000);
l_domdoc dbms_xmldom.DOMDocument;
l_nodelist dbms_xmldom.DOMNodeList;
l_node dbms_xmldom.DOMNode;
l_value VARCHAR2(30);
l_xmltype XMLTYPE;
l_empx XMLTYPE;
l_index PLS_INTEGER;
l_col_ind PLS_INTEGER;
BEGIN
l_doc := '<employees>
<emp>
<name>Scott</name>
<favorites>
<color>red</color>
<color>orange</color>
</favorites>
</emp>
<emp>
<name>John</name>
<favorites>
<color>blue</color>
<color>green</color>
</favorites>
</emp>
</employees>';
l_domdoc := dbms_xmldom.newDomDocument(l_doc);
-- Method 1
dbms_output.put_line('Method 1');
l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/employees/emp/name');
FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP
l_node := dbms_xmldom.item(l_nodelist, cur_emp);
l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
dbms_output.put_line('Emp Name: '||l_value);
END LOOP;
-- Method 2
dbms_output.new_line;
dbms_output.put_line('Method 2');
l_nodelist := dbms_xmldom.getelementsbytagname(l_domdoc, 'name');
-- get first item from list, could loop as shown above
l_node := dbms_xmldom.item(l_nodelist, 0);
l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
dbms_output.put_line('Emp Name: '||l_value);
-- Done with DOMDocument examples, setup for XMLType based examples
dbms_xmldom.freeDocument(l_domdoc);
l_xmltype := XMLTYPE(l_doc);
-- Method 3
dbms_output.new_line;
dbms_output.put_line('Method 3');
l_index := 1;
WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
LOOP
l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();
dbms_output.put_line('Emp Name: '||l_value);
l_index := l_index + 1;
END LOOP;
-- Method 4
dbms_output.new_line;
dbms_output.put_line('Method 4');
l_value := l_xmltype.extract('/employees/emp[2]/name/text()').getStringVal();
dbms_output.put_line('Emp Name: '||l_value);
-- Method 5
dbms_output.new_line;
dbms_output.put_line('Method 5');
l_index := 1;
WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
LOOP
l_empx := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']');
l_col_ind := 1;
WHILE l_empx.Existsnode('/emp/favorites/color[' || To_Char(l_col_ind) || ']') > 0
LOOP
l_value := l_empx.extract('/emp/favorites/color[' || To_Char(l_col_ind) || ']/text()').getStringVal();
dbms_output.put_line('Color: '||l_value);
l_col_ind := l_col_ind + 1;
END LOOP;
l_index := l_index + 1;
END LOOP;
END;
Running this script produces
Method 1
Emp Name: Scott
Emp Name: John
Method 2
Emp Name: Scott
Method 3
Emp Name: Scott
Emp Name: John
Method 4
Emp Name: John
Method 5
Color: red
Color: orange
Color: blue
Color: green
In PL/SQL, you basically have two methods. The first, and older method, is via dbms_xmldom/dbms_xslprocessor and the second is via XMLType. The first method uses DOMDocuments, DOMNodes, DOMNodelist, etc to slowly break down the XML and parse/traverse it. This is based on the W3C definition of a DOM. The second method uses XPath 1.0 syntax to quickly parse an XMLType and return either another XMLType fragment or a basic data type. My preference for parsing XML is via an XMLType as the code seems cleaner to write, understand, and maintain.
Methods 1 and 2 use the old style to parse the XML.
Method 1 used dbms_xmlprocessor to select all the nodes that match the XPath /employees/emp/name and then iterates over the list that is returned to print out all the names.
Method 2 uses dbms_xmldom to get all the nodes called "name" and simply prints the first one. It too could easily loop through all the name nodes since both operations return a DOMNodeList.
Methods 3 through 5 use XMLType to parse the XML.
Method 3 looks for and returns the same information as Method 1. This difference is that instead of returning a list of nodes that match the criteria, it goes out and gets each occurrence one at a time.
Method 4 is like Method 2 in that it returns the contents of a specific node directly, in this case, the name of the second employee "emp[2]/name"
Method 5 shows the name for each employee and their favorite colors. As the code shows, it extract each emp as an XMLType and then parses that XMLType to extract all the colors.
Gotchas:
- /text() refers to the text contents of the given node. Leaving this off returns the node itself (an XMLType).
- .getStringVal() and others, along with many examples can be found in the XMLType Operations section within the online Oracle Documentation.
- "ORA-30625: method dispatch on NULL SELF argument is disallowed" means your XPath returned returned nothing aka NULL and further operations failed. This is common when doing a .get*Val() on a .extract() that returned NULL. One way is to do a .ExistsNode before the .extract().get*Val()
