B Oracle Data Integrator Driver for XML Reference

B.2.1 XML to SQL Mapping

The XML to SQL Mapping is a complex process that is used to map a hierarchical structure (XML) into a relational structure (schema). This mapping is automatic.

Elements and Attributes Mapping

The XML driver maps XML elements and attributes the following way:

• Elements are mapped as tables with the same name.

• Attributes are mapped as columns named like the attributes. Each column is created in the table representing the attribute's element.

Hierarchy & Order Mapping

Extra data may appear in the relational structure as follows:

• In order to map the hierarchy of XML elements, or a one-to-many relation between elements, the XML driver generates in each table corresponding to an element the following extra columns:

  • <element_name>PK: This column identifies the element.

  • <parent_element_name>FK: This column links the current element to its parent in the hierarchy. It contains a value matching the parent element's <element_name>PK value.

• Records in a table, unlike elements in an XML file, are not ordered, unless a specific column is used to define the order. The driver generates also a column named <element_name>ORDER to preserve the order of the elements. When adding new rows in the relational schema, make sure that the ORDER column is correctly set to have the elements correctly ordered under the parent element.

• The root of the hierarchy is identified by a root table named after the root element. This table contains a single records with the following columns:

  • <root_element_name>PK: All level 1 sub-elements will refer to this PK entry.

  • SNPSFILENAME: This column contains the names of the XML file loaded into this schema.

  • SNPSFILEPATH: This column contains the XML file path.

  • SNPSLOADDATE: This column contains the date and time when the file was loaded into the schema.

  The values in this table are managed by the driver and should not be modified.

Mapping Exceptions

This section details some specific situations for the mapping of extra data.

• Elements containing only #PCDATA are not mapped as tables, but as columns of the table representing their parent element. These columns are named <element_name>DATA.

• List Attributes are mapped as a new table with a link (PK, FK) to the table representing the element containing the list.

• XML elements and attributes with names that match SQL keywords are automatically renamed (an underscore is added after their name) in the relational structure to avoid naming conflict between table/column names and SQL keywords. For example, an element named SELECT will be mapped to a table named SELECT_. Such elements are restored in the XML file with their original naming when a synchronize operation takes place.

Note that extra objects created by the driver are used to keep the XML file consistency. These records must be loaded in the relational schema before it is synchronized to an XML file.

B.2.2 XML Namespaces

The XML driver supports XML namespaces (xmlns:) specified for XML attributes and elements.

Elements or attributes specified with a namespace (using the syntax <namespace>:<element or attribute name>) are mapped as tables or columns prefixed with the namespace using the syntax: <namespace>_<element or attribute name>. When synchronizing the XML data back to the file, the namespace information is automatically generated.

B.2.3 Managing Schemas

A schema corresponds to the concept used in Oracle database and other RDBM systems and is a container that holds a set of relational tables. A schema is a generic relational structure in which an entire set of XML file instances may be successfully parsed and extracted. The identified elements and attributes are inserted in the appropriate relational tables and fields.

This schema is generated by the XML driver from either an XML instance file, a DTD file, or an XSD file. It is recommended to generate the schema from a DTD or XSD file.

Note that only a single DTD or XSD file may be referenced in definition of an XML data server URL. In this case, this DTD or XSD may be considered as a master DTD or XSD file if the artifact includes references to other DTD / XSD files. Note that in certain cases multiple schemas may be required. In this case use the add_schema_bundle property.

SELECT col1, schema2.table2.col2, table1.col3 FROM table1, schema2.table2.

B.2.4 Locking

When accessing an XML file, the driver locks it in order to prevent other instances of the driver to connect to the file. The lock file has the same name as the XML file but an .lck extension.

If the driver is incorrectly disconnected, a lock may remain on the file. To remove it, delete the .lck file. It is also possible to unlock an XML file with the UNLOCK FILE command.

B.2.5 XML Schema (XSD) Support

XSD is supported by the XML driver for describing XML file structures. See Section B.7, "XML Schema Supported Features" for more information.

In addition, the XML driver supports document validation against XSD schemas specified within the XML file. This operation may be performed using the VALIDATE driver specific command.

B.3.1 Driver Configuration

This section details the driver configuration.

• The driver name is: com.sunopsis.jdbc.driver.xml.SnpsXmlDriver

• The URL Syntax is: jdbc:snps:xml?[property=value&property=value...]

The properties for the URL are detailed in Table B-1.

Table B-2 listsURL samples.

B.3.2 Automatically Create Multiple Schemas

It is possible to automatically create additional schemas with different XML structures when creating the connection with the driver. This is performed by:

• Declaring in the add_schema_bundle URL property a property file that contains a list of JDBC URLs corresponding to the different additional schemas to create.

• Declaring in the add_schema_path URL property a directory that contains a set of XSD files. For each XSD file an additional schema is created in the built-in engine, based on the XML schema description.

• Specifying additional valid driver URLs as JDBC properties, named addschema_X (X is a number). An additional schema will be created for each URL found in a JDBC property called addschema_X.

Note that all these additional schemas are automatically dropped when their last connection is closed.

These schemas are created in addition to the one that may be created with the properties specified in the JDBC driver URL

B.3.3 Using an External Database to Store the Data

In most cases, the XML driver stores the relational schema mapping of the XML schema in a built-in engine. It is also possible to make the driver store the relational schema in an external relational database.

Using an external database

To use an external database, you must have:

• A file with the .properties extension. This External Database Properties File contains the properties of a JDBC connection to the relational database schema.

• This .properties file in the classpath of Oracle Data Integrator that uses the XML driver. Typically, you can install it with your custom drivers.

• The db_props property in the JDBC URL set to the name of the properties file (without the extension .properties).

It is possible to set or override the external database properties on the URL. These properties must be prefixed with the string dp_. For example:

jdbc:snps:xml?file=/temp/payload.xml&dp_driver=<external_db_driver>&dp_url=<external_db_url>

External Database Properties File

The external database properties file is a text file containing a set of lines with on each line a <property>=<value> pair:

The valid properties are described inTable B-3.

The following sample is an example of a property file for using an Oracle Database as the external storage:

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@HOST:PORT:SID
user=USER_NAME
password=ENCODED_PASSWORD
schema=USER_NAME
drop_on_connect=Y
create_tables=AUTO
create_indexes=Y
truncate_before_load=Y
ids_in_db=Y
drop_tables_on_drop_schema=Y
use_prepared_statements=Y
use_batch_update=Y
batch_update_size=30
commit_periodically=Y
num_inserts_before_commit=1000
reserve_chars_for_column=3
reserve_chars_for_table=3

The following sample is an example of a property file for using a Microsoft SQL Server database as the external storage:

driver=com.microsoft.jdbc.sqlserver.SQLServerDriverurl=jdbc:microsoft:sqlserver://SERVER_NAME:PORT;SelectMethod=cursoruser=USER_NAMEpassword=ENCODED_PASSWORDschema=OWNNER_NAMEdrop_on_connect=Ycreate_tables=AUTOcreate_indexes=Ytruncate_before_load=Yids_in_db=Ydrop_tables_on_drop_schema=Yuse_prepared_statements=Yuse_batch_update=Ybatch_update_size=30commit_periodically=Ynum_inserts_before_commit=1000reserve_chars_for_column=3reserve_chars_for_table=3

B.4.1 CREATE FILE

Generate an XML file called <file_name> from the default schema data, or from a specific schema.

If the EMPTY option is specified, an empty file with the XML structure specified in the DTD or XSD is generated.

CREATE [EMPTY] FILE <file_name> [FROM SCHEMA <schema_name>] 
    [JAVA_ENCODING <java_encoding>  XML_ENCODING <xml_encoding>] 
    [NO_CLOSING_TAGS] [NO_DEFAULT_NS]

Parameters 

FROM SCHEMA

Specify the schema in which data will be written in the XML file.

JAVA_ENCODING

Encoding of the generated File.

XML_ENCODING

Encoding generated in the file's xml tag.

Example of generated tag: <?xml version="1.0" encoding="ISO-8859-1"?>

Note that Java and XML encoding should always be specified together.

NO_CLOSING_TAG

If this parameter is specified, the driver generates the empty tags with closing tag. By default, the driver generates an empty element as <element></element>. with the no_closing_tags parameter, it generates <element/>.

NO_DEFAULT_NS

If this parameter is specified, the driver generates the target file without a default namespace entry.

Remarks

• If the file name contains spaces, enclose it in double quotes

• The encoding values should be enclosed in double quotes as they may contain special characters.

B.4.2 CREATE XMLFILE

Generate an XML file called <file_name> from the default schema data, or from a specific schema.

CREATE XMLFILE <file_name> [FROM SCHEMA <schema_name>] 
    [JAVA_ENCODING <java_encoding> XML_ENCODING <xml_encoding>] 
    [NO_CLOSING_TAGS][NO_DEFAULT_NS] 

Parameters 

FROM SCHEMA

Specify the schema in which data will be written in the XML file.

JAVA_ENCODING

Encoding of the generated File.

XML_ENCODING

Encoding generated in the file's xml tag. Example of generated tag: <?xml version="1.0" encoding="ISO-8859-1"?>.

Note that Java and XML encoding should always be specified together.

NO_CLOSING_TAG

If this parameter is specified, the driver generates the empty tags with closing tag. By default, the driver generates an empty element as <element></element>. with the no_closing_tags parameter, it generates <element/>.

NO_DEFAULT_NS

If this parameter is specified, the driver generates the target file without a default namespace entry.

Remarks

• If the file name contains spaces, enclose it in double quotes

• The encoding values should be enclosed in double quotes as they may contain special characters.

B.4.3 CREATE FOREIGNKEYS

Create physically all the foreign keys joining the tables from the relational schema in the database. This command is helpful to enforce integrity constraints on the schema.

CREATE FOREIGNKEYS

Remarks

After using CREATE FOREIGNKEYS, it is not possible any longer to perform a LOAD FILE.

B.4.4 CREATE SCHEMA

Create in <schema_name> an empty schema or a schema with tables mapping the structure of the description file specified as <dtd/xsd_name>.

CREATE SCHEMA <schema_name> [WITH DTD <dtd/xsd_name>] [REPLACE] 
   [ROOTELT <root element>] [READONLY] 
   [JAVA_ENCODING <java_encoding> XML_ENCODING <xml_encoding>]

Parameters 

WITH DTD

Specify the description file (DTD or XSD) which structure will be created in the schema.

REPLACE

Specify if an existing schema structure must be replaced with the new one.

ROOTELT

Element in the description file considered as the root of the XML file. This element name is case sensitive.

READONLY

The schema loaded cannot have data inserted, deleted or updated.

JAVA_ENCODING

Encoding of the target XML file(s) generated from schema.

Note: Java and XML encoding should always be specified together.

XML_ENCODING

Encoding generated in the target files' XML tag. Example of generated tag: <?xml version="1.0" encoding="ISO-8859-1"?>.

Remarks

• The XML file data is not loaded. This command is similar to LOAD FILE but does not load the XML file data.

• The schema is created in READONLY mode since no XML file is associated with it.

• The connection schema does not automatically switch to the newly created schema.

• If the file name contains spaces, enclose the name id double quotes.

• The encoding values should be enclosed in double quotes as they may contain special characters.

B.4.5 DROP FOREIGNKEYS

Drop all the foreign keys on the tables of the relational schema in the database. This command is helpful to drop all integrity constraints on the schema.

DROP FOREIGNKEYS

B.4.6 DROP SCHEMA

Drop an existing schema. If <schema_name> is not specified, the current schema is dropped. It is not possible to drop a schema if there are pending connections to this schema. Trying to drop a schema with existing connections causes an exception.

DROP SCHEMA [<schema_name>]

B.4.7 LOAD FILE

Load the <file_name> XML file into the current relational schema.

LOAD FILE <file_name> [WITH DTD <dtd/xsd_name> | INSERT_ONLY] [ON SCHEMA <schema_name>] [REPLACE] [READONLY] [ROOTELT <root element>] [AUTO_UNLOCK] [DB_PROPS <external database properties>]

Parameters 

WITH DTD

Specify the description file (DTD or XSD) which structure will be created in the schema.

INSERT_ONLY

Adds the data from the XML file in the schema if it already exists. The new XML file should have valid description file for the existing schema.

ON SCHEMA

Force the file to be loaded in <schema_name>. Note that the current schema is not set after the command automatically to <schema_name>.

REPLACE

Specify if an existing schema structure with the same name must be replaced with the one that is being loaded.

READONLY

The schema loaded cannot have data inserted, deleted or updated.

ROOTELT

Element in the description file considered as the root of the XML file. This element name is case sensitive.

AUTO_UNLOCK

If the XML file is already locked by another driver instance, an exception occurs unless the AUTO_UNLOCK is specified. This parameter unlocks automatically the file if it is locked.

DB_PROPS

Loads the file in the external database identified by the properties file called <external database properties>.properties.

Remarks

• If the file name contains spaces, enclose the name in double quotes.

• When no schema is specified, the driver automatically generates a schema name from the file name.

• The connection schema does not automatically switch to the loaded schema.

• If the XML file is already open in another schema, an exception occurs.

B.4.8 SET SCHEMA

Set the current schema to <schema_name>.

SET SCHEMA <schema_name>

Remarks

If no <schema_name> is specified, the schema is set to the default schema.

B.4.9 SYNCHRONIZE

Synchronize data in the schema with the file data.

SYNCHRONIZE [ALL | SCHEMA <schema_name>] [FROM FILE/FROM DATABASE] 
 [IGNORE CONFLICTS]

Parameters 

ALL

Synchronizes all schemas

SCHEMA

Synchronizes only <schema_name>

FROM FILE

Forces the data to be loaded from the file to the schema. Erases all changes in the schema.

FROM DATABASE

Forces the data to be loaded from the schema to the file. Erases all changes in the file.

IGNORE CONFLICTS

If FROM FILE/DATABASE are not specified, the driver automatically determines where data have been modified (in the FILE or DATABASE) and updates the unmodified data. If both the FILE and the DATABASE have been modified, the driver issues a Conflict Error. if the IGNORE CONFLICTS parameter is used, no error is issued, and if performing a SYNCHRONIZE ALL, the following schemas will be synchronized.

B.4.10 UNLOCK FILE

Unlocks <file_name> if it is locked by another instance of the driver.

UNLOCK FILE <file_name>

B.4.11 TRUNCATE SCHEMA

Clears all data from the current schema, or from <schema_name>.

TRUNCATE SCHEMA [<schema_name>]

B.4.12 VALIDATE

Validates an XML file <file_name> content against the XML Schema (XSD) referenced in the XML file. This command returns an exception if the file is not valid.

VALIDATE [FILE <file_name>] [ERROR_ON_WARNING|IGNORE_ON_WARNING] 
   [ERROR_ON_ERROR|IGNORE_ON_ERROR] 
   [ERROR_ON_FATAL_ERROR|IGNORE_ON_FATAL_ERROR] [VERBOSE]

Parameters 

FILE <file_name>

Name of the XML file to validate.

ERROR_ON_WARNING | IGNORE_ON_WARNING

Ignore or generate errors on XSD validation warnings, such as values out of range. The default value is IGNORE_ON_WARNING.

ERROR_ON_ERROR | IGNORE_ON_ERROR

Ignore or generate errors on XSD validation errors, such as non conform attribute or element. The default value is ERROR_ON_ERROR.

ERROR_ON_FATAL_ERROR | IGNORE_ON_FATAL_ERROR

Ignore or generate errors on XSD validation fatal errors, such as malformed XML. The default value is ERROR_ON_FATAL_ERROR.

VERBOSE

Displays on the Java console the detailed errors and number of the line causing the error. Nothing is displayed by default on the console.

B.5.1 SQL Statements

Any number of commands may be combined. You can optionally use the semicolon character (;) to separate each command.

This section details the following commands:

• COMMIT

• CREATE TABLE

• DELETE

• DISCONNECT

• DROP TABLE

• INSERT INTO

• ROLLBACK

• SELECT

• SET AUTOCOMMIT

• UPDATE

• Expressions, Condition and Values

COMMIT [WORK]

CREATE TABLE <table_name> 
  ( <columnDefinition> [, ...] [, <constraintDefinition>...])

<columnDefinition> ::=
    <column_name> <datatype> [(anything)] [[NOT] NULL] [IDENTITY] [PRIMARY KEY]

<constraintDefinition> ::=
[ CONSTRAINT <constraint_name> ]
    UNIQUE ( <column_name> [,<column>...] ) |
    PRIMARY KEY ( <column_name> [,<column_name>...] ) |
    FOREIGN KEY ( <column_name> [,<column_name>...] ) 
    REFERENCES <referenced_table> ( <column_name> [,<column_name>...] )

DELETE FROM <table_name> [ WHERE <expression> ]

DISCONNECT

DROP TABLE <table_name>

INSERT INTO <table_name> [ ( <column_name> [,...] ) ] 
    { VALUES (<expression> [,...]) | <SELECT Statement> }

ROLLBACK

SELECT [DISTINCT] { <select_expression> | <table_name>.* | * } [, ... ]
[  INTO <new_table> ]
   FROM <table_list>
[  WHERE <expression> ]
[  GROUP BY <expression> [, ...] ]
[  ORDER BY <order_expression> [, ...] ]
[  { UNION [ALL] | {MINUS|EXCEPT} | INTERSECT } <select_statement> ]

<table_list> ::=
     <table_name> [ { INNER | LEFT [OUTER] } JOIN <table_name> 
     ON <expression> ]  [, ...]

<select_expression> ::=
      { <expression> | COUNT(*) | {COUNT | MIN | MAX | SUM | AVG} 
       (<expression>) <column_alias>}

<order_expression> ::=
      { <column_number> | <column_alias> | <select_expression> } [ ASC | DESC ]

SET AUTOCOMMIT { TRUE | FALSE }

UPDATE table SET column = <expression> [, ...] [WHERE <expression>]

<expression> ::=
     [NOT] <condition> [ { OR | AND } <condition> ]

<condition> ::=
     { <value> [ || <value> ]
      | <value> { = | < | <= | > | >= | <> | != | IS [NOT] } <value>
      | EXISTS(<select_statement>)
      | <value> BETWEEN <value> AND <value>
      | <value> [NOT] IN ( {<value> [, ...] | selectStatement } )
      | <value> [NOT] LIKE <value> [ESCAPE] value }

<value> ::=
      [ + | - ] { term [ { + | - | * | / } term ]
      | ( condition )
      | function ( [parameter] [,...] )
      | selectStatement_giving_one_value

<term> ::=
      { 'string' | number | floatingpoint | [table.]column | TRUE | FALSE | NULL }

<string> ::=

<name> ::=

<values> ::=

B.5.2 SQL FUNCTIONS

Table B-4 lists the numerical functions.

Table B-5 lists the string functions.

Table B-6 lists the date/time functions.

Note that a date value starts and ends with a single quote ('), the format is yyyy-mm-dd (see java.sql.Date). A time value starts and ends with a single quote ('), the format is hh:mm:ss (see java.sql.Time).

Table B-7 lists the system functions.

B.7.1 Datatypes

The following datatypes are supported:

• These datatypes are converted to String columns: string, normalizedString, token, nmtoken, nmtokens, anyUri, id, idref, date, datetime, time, hexBinary

• These datatypes are converted to Integer columns: int, positiveInteger, negativeInteger, nonNegativeInteger, onPositiveInteger, long, unsignedLong, unsignedInt, short, unsignedShort, byte, unsignedByte, boolean (Boolean are converted to a numeric column with 0 or 1, but they can take "true" or "false" values from the input files)

• These datatypes are converted to Decimal (with 2 decimal places) columns: decimal, float, double

B.7.2 Supported Elements

This section lists all schema elements. Supported syntax elements are shown in bold. Unsupported syntax elements are shown in regular font. They are ignored by the driver.

This section details the following schema elements:

• All

• Attribute

• AttributeGroup

• Choice

• ComplexContent

• ComplexType

• Element

• Extension

• Group

• Import

• Include

• List

• Restriction

• Schema

• Sequence

• SimpleContent

• SimpleType

<all
    id=ID
    maxOccurs=1
    minOccurs=0|1
    any attributes
>
(annotation?,element*)
</all>

<attribute
    default=string
    id=ID
    name=NCName
    type=QName
    use=optional|prohibited|required
    ref=QName
    fixed=string
    form=qualified|unqualified
    any attributes
>
(annotation?,(simpleType?))
</attribute>

<attributeGroup
    id=ID
    name=NCName
    ref=QName
    any attributes
>
(annotation?),((attribute|attributeGroup)*,anyAttribute?))
</attributeGroup>

<choice
    id=ID
    maxOccurs=nonNegativeInteger|unbounded
    minOccurs=nonNegativeInteger
    any attributes
>
(annotation?,(element|group|choice|sequence|any)*)
</choice>

<complexContent
    id=ID
    mixed=true|false
    any attributes
>
(annotation?,(restriction|extension))
</complexContent>

<complexType
    name=NCName
    id=ID
    abstract=true|false
    mixed=true|false
    block=(#all|list of (extension|restriction))
    final=(#all|list of (extension|restriction))
    any attributes
>
(annotation?,(simpleContent|complexContent|((group|all|choice|sequence)?,((attribute|attributeGroup)*,anyAttribute?))))
</complexType>

<element
    name=NCName
    maxOccurs=nonNegativeInteger|unbounded
    minOccurs=nonNegativeInteger
    type=QName
    id=ID
    ref=QName
    substitutionGroup=QName
    default=string
    fixed=string
    form=qualified|unqualified
    nillable=true|false
    abstract=true|false
    block=(#all|list of (extension|restriction))
    final=(#all|list of (extension|restriction))
    any attributes
>
annotation?,((simpleType|complexType)?,(unique|key|keyref)*))
</element>

<extension
    id=ID
    base=QName
    any attributes
>
(annotation?,((group|all|choice|sequence)?,((attribute|attributeGroup)*,anyAttribute?)))
</extension>

<group
    id=ID
    name=NCName
    ref=QName
    maxOccurs=nonNegativeInteger|unbounded
    minOccurs=nonNegativeInteger
    any attributes
>
(annotation?,(all|choice|sequence)?)
</group>

<import
    id=ID
    namespace=anyURI
    schemaLocation=anyURI
    any attributes
>
(annotation?)
</import>

<include
    id=ID
    schemaLocation=anyURI
    any attributes
>
(annotation?)
</include>

<list
    id=ID
    itemType=QName
    any attributes
>
(annotation?,(simpleType?))
</list>

<restriction
     id=ID
     base=QName
     any attributes
>
Content for simpleType:
(annotation?,(simpleType?,(minExclusive|minInclusive|maxExclusive|maxInclusive|
totalDigits|fractionDigits|length|minLength|maxLength|enumeration|whiteSpace|
pattern)*))
Content for simpleContent:
(annotation?,(simpleType?,(minExclusive|minInclusive|maxExclusive|maxInclusive|
totalDigits|fractionDigits|length|minLength|maxLength|enumeration|whiteSpace|
pattern)*)?, ((attribute|attributeGroup)*,anyAttribute?))
Content for complexContent:
(annotation?,(group|all|choice|sequence)?, ((attribute|attributeGroup)*,anyAttribute?))
</restriction>

<schema
     id=ID
     attributeFormDefault=qualified|unqualified
     elementFormDefault=qualified|unqualified
     blockDefault=(#all|list of (extension|restriction|substitution))
     finalDefault=(#all|list of (extension|restriction|list|union))
     targetNamespace=anyURI
     version=token
     xmlns=anyURI
     any attributes
>
((include|import|redefine|annotation)*,(((simpleType|complexType|group|
attributeGroup)|element|attribute|notation),annotation*)*)
</schema>

<sequence
     id=ID
     maxOccurs=nonNegativeInteger|unbounded
     minOccurs=nonNegativeInteger
     any attributes
>
(annotation?,(element|group|choice|sequence|any)*)
</sequence>

<simpleContent
     id=ID
     any attributes
>
(annotation?,(restriction|extension))
</simpleContent>

<simpleType
     name=NCName
     id=ID
     any attributes
>
(annotation?,(restriction|list|union))
</simpleType>

B.7.3 Unsupported Features

The following elements and features are not supported or implemented by the XML driver.