Reading XML data

Here is a simple example of reading xml data with an SQL statement:

select extractvalue(value(a), '//Id'),
       extractvalue(value(a), '//FirstName'),
       extractvalue(value(a), '//LastName')
  from table(xmlsequence(extract(xmltype('<People>
                                           <Person>
                                            <Id>1</Id>
                                            <FirstName>First1</FirstName>
                                            <LastName>Last1</LastName>
                                           </Person>
                                           <Person>
                                            <Id>2</Id>
                                            <FirstName>First2</FirstName>
                                            <LastName>Last2</LastName>
                                           </Person>
                                          </People>')
                                          ,'//Person' ))) a

If you have more complex xml you can read data using XPath expressions.

For reading XML into objects of registered types I wrote a function that returns the registered object based on the input parameter. This function also validates the xml data against the registered schema.

create or replace function to_people(p_xml clob) return people_t is
 v_people people_t;
 v_xml xmltype;
 v_schema_name varchar2(10) := 'People.xsd';
 v_schema_elem varchar2(10) := 'People';
begin
 v_xml := xmltype(p_xml);
 IF v_xml.isSchemaValid(v_schema_name, v_schema_elem) = 1 THEN
  v_xml.extract('*').toobject(v_people,v_schema_name,v_schema_elem);
  dbms_output.put_line('Valid');
  return v_people;
 ELSE
  dbms_output.put_line('Invalid');
  return null; 
 END IF;
end;

If you have a table (e.g. person_table) that has an element (e.g. data) of type person_t, you can easily read data from the table using SQL statements.

select pe.ID, pe.FIRSTNAME, pe.LASTNAME 
 from people_table p,
      table(p.people.data) pe