Chapter 6 Exchanging data with databases supporting XML

Generating an annotated schema for Oracle 9i2

Oracle 9i2 is a database server with a native XML storage and retrieval technology called Oracle XML DB. There is no mapping between XML data and relational data. Tables, columns and abstract data types (ADT) are created from annotated schemas (XSDs). Annotated schemas are XML-coded files, targeted with an XML language and tagged with specific DBMS annotations, that allow you to store or retrieve data in an XML format, from relational databases supporting XML.

An XML model allows you to generate an annotated schema (XSD) for Oracle 9i2. You just need to attach the Oracle 9i2 extended model definition to the XML model. Oracle 9i2 uses by default the name of the XML elements present in the annotated schema to generate SQL objects. You can override the creation of SQL objects by defining extended attributes for elements, complex types and the XML model.

WARNING! 

Caution
The following procedure assumes you have an XML model open in the workspace and targeted with XSD.

Steps To generate an annotated schema for Oracle 9i2:

  1. Select Model→Extended Model Definitions.

    The List of Extended Model Definitions is displayed.
  2. Click the Import an Extended Model Definition tool.

    The Extended Model Definition Selection dialog box is displayed.
  3. In the XML in Database tab, select Oracle XML DB 9.2.
  4. Click OK.

    Oracle XML DB 9.2 is displayed in the List of Extended Model Definitions.
  5. Click OK.

    The extended model definition is displayed in the Browser tree view, in a folder attached to the XML model.
  6. Double-click an element symbol in the diagram to display its property sheet.
  7. In the Extended Attributes tab, type the name of a table or a column in the Value column of the SQLName annotation, if you want to create a table or a column from the selected element.


    You can define a value for the following annotations:
    Annotation Description
    beanClassname Can be used within element declarations. If the element is based on a global complexType, this name must be identical to the beanClassname value within the complexType declaration. If a name is specified by the user, the bean generation will generate a bean class with this name, instead of generating a name from the element name
    columnProps Specifies the column storage clause that is inserted into the default CREATE TABLE statement. It is useful mainly for elements that are mapped to tables, namely top-level element declarations and out-of-line element declarations
    defaultTable Specifies the name of the table into which XML instances of this schema should be stored. This is most useful in cases when the XML is being inserted from APIs where table name is not specified (for example, FTP and HTTP)
    javaClassname Used to specify the name of a Java class that is derived from the corresponding bean class, to ensure that an object of this class is instantiated during bean access. If a JavaClassname is not specified, Oracle XML DB will instantiate an object of the bean class directly
    maintainDOM If true, instances of this element are stored so that they retain DOM fidelity on output. This implies that all comments, processing instructions, namespace declarations, and so on, are retained in addition to the ordering of elements. If false, the output need not be guaranteed to have the same DOM behavior as the input
    maintainOrder If true, the collection is mapped to a VARRAY. If false, the collection is mapped to a NESTED TABLE
    SQLCollSchema Name of the database user owning the type specified by SQLCollType
    SQLCollType Specifies the name of the SQL collection type corresponding to this XML element that has maxOccurs > 1
    SQLInline If true this element is stored inline as an embedded attribute (or a collection if maxOccurs > 1). If false, a REF (or collection of REFs if maxOccurs > 1) is stored. This attribute will be forced to false in certain situations (like cyclic references) where SQL will not support inlining
    SQLName Specifies the name of the attribute within the SQL object that maps to this XML element
    SQLSchema Name of the database user owning the type specified by SQLType
    SQLType Specifies the name of the SQL type corresponding to this XML element declaration
    tableProps Specifies the TABLE storage clause that is appended to the default CREATE TABLE statement. This is meaningful mainly for global and out-of-line elements
  8. Click OK.
  9. Repeat steps 6 to 8 for each element you want to generate into a table or a column, or for which you want to define extra annotations.
  10. Double-click a complex type symbol in the diagram to display its property sheet.
  11. In the Extended Attributes tab, type the name of an abstract data type (ADT) in the Value column of the SQLType annotation, if you want to create an ADT from the selected complex type.


    You can define a value for the following annotations:
    Annotation Description
    beanClassname Can be used within element declarations. If the element is based on a global complexType, this name must be identical to the beanClassname value within the complexType declaration. If a name is specified by the user, the bean generation will generate a bean class with this name, instead of generating a name from the element name
    SQLSchema Name of the database user owning the type specified by SQLType
    SQLType Specifies the name of the SQL type corresponding to this XML element declaration
  12. Click OK.
  13. Repeat steps 10 to 12 for each complex type you want to generate into an ADT, or for which you want to define extra annotations.
  14. <optional> In the Browser tree view, double-click the model item to display its property sheet.
  15. In the Extended Attributes tab, you can select a value (false or true) for the following annotations:
    Annotation Description
    mapUnboundedStringToLob If true, unbounded strings are mapped to CLOB by default. Similarly, unbounded binary data get mapped to BLOB, by default. If false, unbounded strings are mapped to VARCHAR2(4000), and unbounded binary components are mapped to RAW(2000)
    storeVarrayAsTable If true, the VARRAY is stored as a table (OCT). If false, the VARRAY is stored in a LOB
  16. Click OK.
  17. Select Language→Generate XML Schema Definition File.

    The Generation dialog box is displayed with the extended model definition selected in the Targets tab.
  18. Select a path for the annotated schema with the Select a Path tool beside the Directory box.
  19. Click OK.

    The Result dialog box is displayed with the path of the generated schema file.
  20. Click Edit.

    The annotated schema is displayed in the editor window.


    Note the Oracle namespace (with the sql prefix) and annotations for tables (sql:SQLName) and ADTs (sql:SQLType)
  21. Click Close in the Result dialog box.

 

Copyright (C) 2007. Sybase Inc. All rights reserved.