Perl Cd Bookshelf [Electronic resources] نسخه متنی

Generating XML Schemas

XML schemas and DTDs define the metadata for XML and can ensure both data and structural integrity. In Oracle Database 10g, the DBMS_XMLQUERY PL/SQL package and the XML SQL Utility (XSU) Java package support XML schema generation from simple SQL statements. DBMS_XMLSCHEMA can generate XML schemas only from existing object types.

Taking the employees table as an example and using DBMS_XMLQUERY, we can generate the corresponding XML schema:

SET AUTOPRINT ON
VAR result CLOB;
BEGIN
:result:=DBMS_XMLQUERY.GETXML('SELECT employee_id AS "@id",
email, department_id
FROM employees WHERE rownum=1',
DBMS_XMLQUERY.SCHEMA);
END;

The generated XML schema is

<?xml version = '1.0'?>
<DOCUMENT xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="ROWSET">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ROW" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EMAIL" nillable="true" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="25"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="DEPARTMENT_ID" 
type="xsd:integer"
 nillable="true" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="num" type="xsd:integer"/>
<xsd:attribute name="id" type="xsd:integer"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<ROWSET xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:noNamespaceSchemaLocation="#/DOCUMENT
/xsd:schema[not(@targetNamespace)]">
<ROW num="1" id="100">
<EMAIL>SKING</EMAIL>
<DEPARTMENT_ID>90</DEPARTMENT_ID>
</ROW>
</ROWSET>
</DOCUMENT>

The generated XML schema maps database data types to the corresponding XML schema data types. In this case, VARCHAR2 maps to xsd:string and NUMBER maps to xsd:integer. The generated schema also includes the length constraints for the VARHAR2 columns using the xsd:maxLength for xsd:string. For table columns that can be NULL, the attribute xsd:nillable=“true” is added. The generated XML documents specify the schema location using the special syntax in Oracle XML DB, which uses # to specify the current document followed by the XPath to the XML schema definition. Oracle XML schema processors are extended to validate the XML document using this “inline” XML schema location, though this is not defined in the W3C XML Schema Recommendation.

To generate the DTDs, you can use DBMS_XMLQUERY.DTD as the second parameter for the DBMS_XMLQUERY.GETXML() function, as shown in the following example:

SET AUTOPRINT ON
VAR result CLOB; 
BEGIN
:result:=DBMS_XMLQUERY.GETXML('SELECT * FROM employees 
WHERE rownum=1',
DBMS_XMLQUERY.DTD); 
END;

The XSU Java package provides similar interfaces to those provided by the DBMS_XMLQUERY package.

Using DBMS_XMLSCHEMA, you can create the following object types and use them as parameters as in the following example:

CREATE OR REPLACE TYPE EMP_T AS OBJECT
( "@id" NUMBER(6),
email VARCHAR2(25),
department_id NUMBER(4));
SELECT DBMS_XMLSCHEMA.GENERATESCHEMA('HR', 'EMP_T') FROM dual;

If there is any related object, you must create the corresponding object. Otherwise, you will get the following error:

 PLS-00382: expression is of wrong type

Though using the DBMS_XMLSCHEMA package is complicated, it allows great flexibility in creating XML hierarchical structure. The advantage of using DBMS_XMLQUERY is that you need to pass only in SQL queries. However, it generates limited types of XML schemas.