Simulating a DOM NodeList using Oracle’s Native XMLType
Oracle has had XML support in the form of the XML Toolkit since the release of Oracle8i. The XML Toolkit comes in several flavors: C, Java, and PL/SQL. The PL/SQL flavor is basically a set of PL/SQL wrapped Java classes, and is not native to the RDBMS. In other words, it must be manually loaded into the RDBMS. With the release of Oracle9i, a new native data type called XMLType was introduced.
The XMLType allows us to search and extract data from XML documents via XPath without having to load the XML Toolkit. Oracle’s implementation of XPath is pretty good, put it lacks atomic support for a few basic XPath functions such as count(), first(), and last() and a few others. These functions may be used in a node index such as //parent/child[last()]/text(), but they cannot be used independently to return a numeric count of elements or to return the index of the last value in a certain node set or group of repeating tags.
Using a DOM Parser, we are able generate a Node list or array of nodes (common or repeating sets of tags). With this array, we can determine the number of elements present in each tag group, and are able to parse each group of tags without having to know exactly how many tag groups or nodes the document contains. We are still able to simulate the creation of Node lists using the XMLType and a couple of other new native XML components: XMLSequence function and XMLSequenceType data type. The XMLSequence function returns a VARRAY of XMLType objects given a based XMLType and an XPath search string. The XMLSequence function can only be used in a SELECT statement (i.e it cannot be used as an independent function in a PL/SQL block). The XMLSequenceType contains a sequence or VARRAY of XMLTypes and provides us with some of the same member functions and procedures that are found in the XMLType.
The following code sample illustrates two methods for simulating and traversing a Node list from an XML document represented by a base XMLType object.
DECLARE
/* XML test fragment with repeating tag groups. */
xml_string VARCHAR2(2000) := '<TestDoc>
<Repeat>
<Name>
<Last>Bennett</Last>
<First>Jason</First>
</Name>
</Repeat>
<Repeat>
<Name>
<Last>Jones</Last>
<First>Mike</First>
</Name>
</Repeat>
<Repeat>
<Name>
<Last>Smith</Last>
<First>Mike</First>
</Name>
</Repeat>
<Repeat>
<Name>
<Last>Johnson</Last>
<First>Nick</First>
</Name>
</Repeat>
</TestDoc>';
/* XMLType Object that will represent the document fragment above. */
xmlDoc XMLType := XMLType(xml_string);
/*-----
* This cursor returns a series of XMLTypes or fragments
* given a base XMLType and an XPath search string.
-------*/
CURSOR crsrLastNames(cv_doc XMLType,
cv_path VARCHAR2) IS
SELECT
value(p) XML
FROM TABLE(XMLSequence(extract(cv_doc,cv_path))) p;
/*-----
* This cursor returns a VARRAY of XMLTypes or XMLSequenceType
* given a base XMLType and an XPath search string.
-------*/
CURSOR crsrNodeList(cv_doc XMLType,
cv_path VARCHAR2) IS
SELECT
XMLSequence(extract(cv_doc,cv_path)) XML
FROM dual;
/* XMLSequenceType Object the will contain an array of XMLType Objects */
nodeList XMLSequenceType;
BEGIN
FOR c IN crsrLastNames(xmlDoc,'//*/Repeat/Name/Last') LOOP
dbms_output.put_line(c.XML.extract('//text()').getStringVal());
END LOOP;
OPEN crsrNodeList(xmlDoc,'//*/Repeat/Name');
FETCH crsrNodeList INTO nodeList;
CLOSE crsrNodeList;
FOR x IN 1 .. nodeList.count() LOOP
dbms_output.put_line(nodelist(x).extract('//Last/text()').getStringVal()||','||nodelist(x).extract('//First/text()').getStringVal());
END LOOP;
END;
/
The first method uses a cursor whose returning rows are individual XMLTypes representing a single node group from the set of repeating nodes or repeating tag sets. The second method uses another cursor that returns an object of type XMLSequenceType into an XMLSequenceType object.
10:09:21 PM 
|
