Skyhawk Systems - Simplify data transformations
Products | Download | Buy | About Us
 
Table of Contents Connect XML-2-DB User's Guide mapRoot.htmTable of ContentsprocedureRefInf.htm

5(b). Table Reference Information - <table-reference-information>

In the mapping file, a table reference must be defined for each database table into which data has to be inserted. A table reference is like a short form for referring to a specific table in a specific schema/database. Once a table reference has been defined, this reference can be referred to in other mapping file rules. A table reference is defined by creating a <table-reference-information> element.

For SQL Server, the element <table-reference-information> has the format:

       <!ELEMENT table-reference-information    EMPTY>
       <!ATTLIST table-reference-information
                 table-reference CDATA #REQUIRED
                 table-name      CDATA #REQUIRED
                 database-name   CDATA #IMPLIED
                 object-owner    CDATA #IMPLIED >

For Oracle, the element <table-reference-information> has the format:

       <!ELEMENT table-reference-information    EMPTY>
       <!ATTLIST table-reference-information
                 table-reference CDATA #REQUIRED
                 table-name      CDATA #REQUIRED
                 object-owner    CDATA #IMPLIED >

The attribute table-reference, of the element <table-reference-information>, is a user-defined reference name that will be used to refer to this table in other mapping rules.

For Oracle databases, the attribute object-owner must have the schema name. It defaults to the login id that you connected to, which is provided in the database configuration file. For SQL Server databases, the attribute object-owner must have the object owner name. It defaults to the login id that you connected to, which is provided in the database configuration file.

For SQL Server, the element <table-reference-information> has an additional attribute - database-name. This is an optional attribute, whose value defaults to the database that the user connected to. This is the database in the connection string in the database configuration file.

Some examples of table-reference-information elements are listed below:

      <!--       For SQL Server       -->
      <!--  BillingAddresses is a table reference that refers to the table Billing.TESTUSER.addresses  -->
      <table-reference-information
           table-reference="BillingAddresses"
           table-name="addresses"
           database-name="Billing"
           object-owner="TESTUSER">
      </table-reference-information>
	  
      <!--       For SQL Server       -->
      <!--  SalesAddresses is a table reference that refers to the table Sales.TESTUSER.addresses  -->	  
      <table-reference-information
           table-reference="SalesAddresses"
           table-name="addresses"
           database-name="Sales"
           object-owner="TESTUSER">
      </table-reference-information>
	  
      <!--       For Oracle          -->
      <!--  USER1_ADDRESSES is a table reference that refers to the table USER1.ADDRESSES  -->
      <table-reference-information
           table-reference="USER1_ADDRESSES"
           table-name="ADDRESSES"
           object-owner="USER1">
      </table-reference-information>
	  
      <!--       For Oracle          -->
      <!--  USER2_ADDRESSES is a table reference that refers to the table USER2.ADDRESSES  -->	  
      <table-reference-information
           table-reference="USER2_ADDRESSES"
           table-name="ADDRESSES"
           object-owner="USER2">
      </table-reference-information>

The attribute table-name must be set to the name of the table. For Oracle databases, the value of table-name must have the same case as in Oracle's data dictionary. For tables that were NOT created using quotes around the table name, the table name must be provided in upper case. For example, if a table was created using the following statement, the table-name attribute value must be specified in uppercase.

	  create table Addresses 
	      (street1 varchar(100), ....)
		  
	  <table-reference-information 
	        table-reference="TESTUSER1_ADDRESSES" 
	        table-name="ADDRESSES" 
	        owner="TESTUSER1"/>

If a table was created using quotes, the table-name attribute value must be specified in mixed case.

	  create table "Addresses" 
	      (street1 varchar(100), ....)
		  
	  <table-reference-information 
	        table-reference="TESTUSER1_ADDRESSES" 
	        table-name="Addresses" 
	        owner="TESTUSER1"/>
Copyright Skyhawk Systems. All Rights Reserved.
Send comments and questions to support@skyhawksystems.com.
mapRoot.htmTable of ContentsprocedureRefInf.htm