XML in Oracle PL/SQL - Part-1

To keep abreast with new technologies oracle now integrated XML functionalities into PL/SQL. This articles describes some feautures.

XML Type:

Oracle 9i release 1 introduced a new datatype , XMLType, to facilitate native handling of XML data in database.

Use XMLType anytime you want to use the database as a persistent storage of XML.

XML Type data can be stored in two ways:

1. In Large Objects (LOBs) – LOB storage maintains content fidelity,
also called document fidelity. The original XML is preserved, including whitespace. An entire XML document is stored as a whole in a LOB. For non-schema-based storage, XMLType offers a Character Large Object (CLOB) storage option.

Note: Use this option only when your application requires content fidelity, since this option provides mediocre performance for DML and limited accessibility to SQL features.

Unstructured storage: The content of the XMLType is persisted as XML text using a CLOB datatype. This option is available for non-schema-based and schema-based XML content. When the XML is to be stored and retrieved as complete documents, unstructured storage may be the best solution as it offers the fastest rates of throughput when storing and retrieving XML content.

2. In Structured storage (in tables and views) – Structured storage maintains DOM (Document Object Model) fidelity.

Structured storage of XML documents is based on decomposing the content of the document into a set of SQL objects. These SQL objects are based on the SQL 1999 Type framework. When an XML schema is registered with Oracle XML DB, the required SQL type definitions are automatically generated from the XML schema.
A SQL type definition is generated from each complexType defined by the XML schema. Each element or attribute defined by the complexType becomes a SQL attribute in the corresponding SQL type. Oracle XML DB automatically maps the 47 scalar data types defined by the XML Schema Recommendation to the 19 scalar datatypes supported by SQL. A varray type is generated for each element and this can occur multiple times.

The generated SQL types allow XML content, compliant with the XML schema, to be decomposed and stored in the database as a set of objects without any loss of information. When the document is ingested the constructs defined by the XML schema are mapped directly to the equivalent SQL types. This allows Oracle XML DB to leverage the full power of Oracle Database when managing XML and can lead to significant reductions in the amount of space required to store the document. It can also reduce the amount of memory required to query and update XML content.

Annotating an XML schema allows control over the naming of the SQL objects and attributes created. Annotations can also be used to override the default mapping between the XML schema data types and SQL data types and to specify which table should be used to store the data.


Registering XML Schema:

One of the very basic requirment will be to register XML Schema to validate XML docs.

Oracle PL/SQL Package Dbms_Xmlschema have set of utilities to play around XML schema. Procedure RegisterSchema need to be used to register XML Schema.

Example:
The following example register a schema namely mt202.xsd (Xml Schema document) into
oracle database using Dbms_Xmlschema.RegisterSchema procedure.

DECLARE
doc VARCHAR2(2000) :=
'< schema
targetNamespace="http://xmlns.oracle.com/xdb/schemas/Scott/mt202.xsd"
xmlns="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified">
< element name ="mt202">
< complexType >
< sequence>
< element name="tag_20" type="integer" fixed="20"/>
< element name="txnref" >
< simpleType>
< restriction base="string">
< length value="16"/>
< /restriction>
< /simpleType>
< /element>
< element name="tag_21" type="integer" fixed="21"/>
< element name="relref" maxOccurs="1">
< simpleType>
< restriction base="string">
< length value="16"/>
< /restriction>
< /simpleType>
</ element>
</ sequence>
</ complexType>
< /element>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('mt202.xsd', doc);
END;
/

In the above example Variable doc carries complete xsd value. To know more about what is Xsd and syntaxes please refer W3Schools tutorial @
http://www.w3schools.com/schema/schema_intro.asp


Deleting the registered XML Schema:

BEGIN
Dbms_XmlSchema.DeleteSchema('mt202.xsd',Dbms_Xmlschema.DELETE_CASCADE_FORCE);
END;