Writing XML data

Basic Oracle functions that allows you to manipulate xml data are:
- XMLAGG
- XMLCDATA
- XMLCOLATTVAL
- XMLCOMMENT
- XMLCONCAT
- XMLELEMENT
- XMLFOREST
- XMLPARSE
- XMLPI
- XMLQUERY
- XMLROOT
- XMLSEQUENCE
- XMLSERIALIZE
- XMLTABLE
- XMLTRANSFORM

Here is a simple example of an sql statement using xmlelement and xmlagg function:

select xmlelement
       ("People",
           (select xmlagg(xmlelement ("Person",
                          xmlelement("Id", p.id),
                          xmlelement("FirstName", p.firstname),
                          xmlelement("LastName", p.lastname))
                          )
            from person p)
       ).getClobVal()
 from dual

If you are using registered xsd schemas and types, you can just use the following function:

create or replace function from_people (p_people tro004_t) return clob is 
 v_schema_name varchar2(10) := 'People.xsd';
 v_schema_elem varchar2(10) := 'People';
begin
   RETURN xmltype(xmldata => p_people, schema => v_schema_name, element => v_schema_elem).getClobVal;
end;
/

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

Changing XML schema to "Oracle" XML schema

In order to have better control over the XMLschema (or types) which is registered in Oracle DB we have to make some changes to it.
First add a new namespace xmlns:xdb="http://xmlns.oracle.com/xdb", which is the Oracle-supplied XML DB namespace. This will let you use additional "features" in XML schema:
- xdb:SQLType
- xdb:SQLName
- xdb:SQLCollType
- xdb:maintainDOM
- xdb:maintainOrder
- ...
I only wrote the ones we will need in this post.

The SQLType, SQLName, SQLCollType is used to name types, elements and collections and to overide the names given in the xsd schema. I suggest you use uppercase characters (this way you will avoid using double quotes when reffering to the elements,type,... (e.g. "Person"."Id")).
The usege of this three atributes is recommended, because this way, the names of types don't get automaticaly generated and you can be sure the name stays the same, even if you reregister the schema.

The default value of maintainDom is true. It insures DOM fidelity. Document Object Model (DOM) fidelity is the concept of retaining the structure of a retrieved XML document, compared to the original XML document, for DOM traversals. DOM fidelity is needed to ensure the accuracy and integrity of XML documents stored in Oracle XML DB.
The default value automaticaly generates a SYS_XDBPD$ element where there is a complex type. This element is of type xdb.xdb$raw_list_t.

If the XML element is declared with attribute maxOccurs > 1, then it is mapped to a collection attribute in SQL. The collection could be a varray value (default) or nested table if the maintainOrder attribute is set to false.

If you will be using the registered schema for creating objects of generated types I suggest you set the value to false for both (maintainDom and maintainOrder). It will be easier to create and manipulate objects based on their types.

The schema from the first post would now look like this:

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xdb="http://xmlns.oracle.com/xdb">
<xsd:element name="Id">
  <xsd:simpleType>
   <xsd:restriction base="xsd:integer"></xsd:restriction></xsd:simpleType>
</xsd:element>
<xsd:element name="FirstName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="LastName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="Person" xdb:SQLType="PERSON_T" xdb:maintainDOM="false" xdb:maintainOrder="false">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Id" xdb:SQLName="ID"/>
     <xsd:element ref="FirstName" xdb:SQLName="FIRSTNAME"/>
     <xsd:element ref="LastName" xdb:SQLName="LASTNAME"/>
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
<!-- MAIN ELEMENT -->
<xsd:element name="People" xdb:SQLType="PEOPLE_T" xdb:maintainDOM="false" xdb:maintainOrder="false">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Person" minOccurs="0" maxOccurs="unbounded" xdb:SQLCollType="PERSON_C" xdb:SQLName="PERSON"/>
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
</xsd:schema>

You can get more information here.

Registering XML schema in Oracle

We would like to register the following XML schema in Oracle:

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Id">
  <xsd:simpleType>
   <xsd:restriction base="xsd:integer"></xsd:restriction></xsd:simpleType>
</xsd:element>
<xsd:element name="FirstName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="LastName">
  <xsd:simpleType>
   <xsd:restriction base="xsd:string"><xsd:maxLength value="35" /></xsd:restriction>
  </xsd:simpleType>
</xsd:element>
<xsd:element name="Person">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Id" />
     <xsd:element ref="FirstName" />
     <xsd:element ref="LastName" />
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
<!-- MAIN ELEMENT -->
<xsd:element name="People">
 <xsd:complexType>
   <xsd:sequence>
     <xsd:element ref="Person" minOccurs="0" maxOccurs="unbounded"/>
   </xsd:sequence>
 </xsd:complexType>
</xsd:element>
</xsd:schema>

To register it in the Oracle DB execute the following script:

begin
  dbms_xmlschema.registerSchema(
  'SchemaName.xsd',
  'insert schema here...',
  genTypes => true,
  genTables => false,
  local => true
  );
end;
/

The script registers the schema and also creates types based on the elements in the xsd. If we register xsd schemas this way, the genereted types are not good to use in programs (plsql procedure, function,...) because the names of types are generated internally (if we delete the schema and register the same one again the names are different!!!).

We can see all the registered schemas in oracle:
select * from user_xml_schemas

To delete any registered schema, execute this:

begin
 dbms_xmlschema.deleteSchema('People.xsd',dbms_xmlschema.DELETE_CASCADE);
end;
/

The proper way the xsd schema should look, will be discribed in the next post :)