Here is very high level pseudo code of what is going on:
The code you show is an example of some features of Oracle for working with XML
1) Drop the tables of the example (incase you are running the example a second time... otherwise the code will fail on trying to create tables that already exist).
2) Create a new table named xml_clob with a single column (of type CLOB) which is used for storing XML
3) Create an XML Document based on the data that is in the ALL_OBJECTS table, which is a system table in oracle. This is being done so you have some example data to work with. This is stored in a variable named "c".
4) Put that XML data into a record in the xml_clob table. So now you hae a single record with an XML document with all the information from your ALL_OBJECTS table. The insert statement:
insert into xml_clob
values( '<DATASET>'||c||'</DATASET>' );
wraps the XML with a root element named DATASET and inserts it into a record.
5) Create a table named my_objects with 4 columns to hold the data from the XML after processing of the XML
6) Create a stored procedure named InsertObjects that has one parameter which is used to pass in an XML string. This procedure is where all of the actual work of handling the XML is done. It uses an "Insert Into" to put records into the my_objects table. The data that is inserted it extracted from the XML string that is passed in.
The lines that look like this:
ExtractValue( Value(xml), 'ORA_OBJECT/ID'),
are using XML paths to identify the elements for the data values that are being inserted, and the following code:
XMLSEQUENCE( Extract( XMLTYPE(xmldoc), 'DATASET/*' ) )
makes a temporary table named XML. I have not used the XMLSEQUENCE method, but I am assuming that it takes the results of the Extract method (which is operating on the elements contained in the xmldoc parameter passed in to the stored proc) and formats them as records in a table.
6) This code reads the XML from the xml_clob table record and calls the InsertObjects stored procedure passing the xml string as an argument.
-- the test (processing 10,000 XML rows):
set timing on
select doc into c from xml_clob;
InsertObjects( c );
7) Finally, the following code does a count on the table to show how many records were adeed.
select count(*) from my_objects
Overall, this is interesting code, and shows how XML can be dealt with in an oracle database. However, the xml data is VERY SIMPLE and essentially is a representation of a database table. Often XML will be very complex compared to this, with nested elements and values in attributes as well. As long as your xml is formatted to represent a table this should work.
On the other hand, if your xml is more complex there are probably ways you can deal with this in Oracle PL/SQL... it just isn't something I have worked with or can help with. However, I've done lots of this sort of thing with VB
and C# (and other languages as well). You typically will work with objects that are provided by the language or by components that are available to you such as the msxml components used in VB6 or the System.XML classes in .NET
So... hopefuly that helps a bit.
Woody Z http://www.learntoprogramnow.com