The example in this entry will take a simple XML document containing a repeating record set with an identifying attribute for each record set and turn it into a cursor. Here is the example code:
DECLARE
CURSOR crsrSampleData(cv_sample_data XMLType) IS
SELECT
a.record_id,
a.name,
a.id_no
FROM
XMLTable('for $i in $xml return $i//SampleRecord'
PASSING cv_sample_data as XML
COLUMNS
record_id VARCHAR2(16) PATH '/SampleRecord/@id',
name VARCHAR2(30) PATH '/SampleRecord/SampleName',
id_no NUMBER(10) PATH '/SampleRecord/SampleIdNo') a;
/* Create an XMLType to pass as a cursor variable. */
v_xml_doc XMLType := XMLType('<?xml version="1.0" standalone="yes"?>
<SampleData>
<SampleRecord id="12345678">
<SampleIdNo>1284793</SampleIdNo>
<SampleName>ALLEN</SampleName>
</SampleRecord>
<SampleRecord id="56789345">
<SampleIdNo>345678</SampleIdNo>
<SampleName>JOE</SampleName>
</SampleRecord>
<SampleRecord id="786543456">
<SampleIdNo>4564321</SampleIdNo>
<SampleName>TOM</SampleName>
</SampleRecord>
</SampleData>');
BEGIN
FOR data_rec IN crsrSampleData(v_xml_doc) LOOP
DBMS_OUTPUT.PUT_LINE('Record Id='||data_rec.record_id);
DBMS_OUTPUT.PUT_LINE('Name='||data_rec.name);
DBMS_OUTPUT.PUT_LINE('Id#='||data_rec.id_no);
END LOOP;
END;
/
Execution of the code sample above will produce the following result:
Record Id=12345678
Name=ALLEN
Id#=1284793
Record Id=56789345
Name=JOE
Id#=345678
Record Id=786543456
Name=TOM
Id#=4564321
The XMLTable function is the key piece of code in the example above. The XMLTable function allows us to shred an XML document into a table like structure of our choosing based upon the data contained in the XML document. In the example above, we map our XMLType cursor variable to the $xml XQuery variable in the XQuery statement 'for $i in $xml return $i//SampleRecord' using the PASSING statement. The XQuery statement will return the sequence of SampleRecord tag nodes. Next, in the COLUMNS statement of the XMLTable function, we use XPath statements to map the desired data elements and attributes to column names that we define. We have taken an XML document and put it in a format that is both familiar and easy to handle for the average PL/SQL developer.
