XML Bulk Loader, Version 1.01

XML is rapidly becoming the de facto standard for data exchange over the Internet and across multiple disparate platforms. The ability to populate database tables with data contained in XML documents is a common application requirement. XML Bulk Loader is a set of Java packages for insert/update/delete data contained in XML documents into/from relational databases. An instance of it can be created and used by applications developed using Java language. A sample application can be run from the command line to populate database tables.

You can control the execution of the Bulk Load operation through a number of options provided by the XML Bulk Loader package. One feature not present in many other bulk load tools  that is available in XML Bulk Loader is the ability to execute a single Bulk Load operation to bulk insert/update/delete data into/from multiple database tables related by parent-child relationships implemented using primary and foreign keys. It also provides a higher level of performance when bulk inserting large volumes of data contained in XML documents.

The objective of this paper is to provide a code-driven introduction to the various aspects of programming the XML Bulk Loader Object Model. It is primarily targeted at database application developers who are familiar with the XML features. To understand and apply the information presented in this paper, you will need familiarity with the XML markup language, and XML Document Type Declaration. You will also need to be familiar with the Java programming language and development environment.

To run XML Bulk Loader, you need the following software:

To download the current version of XML Bulk Loader, see:

You can use XML Bulk Loader as middleware -- that is, it sits between your application and a database. Because of this, you can write your own application to use it. For example, you might use the following code to transfer the data in the TestCreate.xml document to the database according to the mapping defined in create_map.dtd. This uses the XMLParser, XMLHandlerImpl, and DBManagerImpl classes from XML Bulk Loader.

 
   // Create a new XMLParser object, XMLHandler and DBManager implementation objects.
   XMLParser parser = new XMLParser(new XMLHandlerImpl(new DBManagerImpl(), "sql_map.properties"));
   
   //Parse the  input
   parser.setEcho(false); // Set off Echo
   parser.setValidating(true); // Will validate documents as they are parsed
   parser.parse(new InputSource("TestCreate.xml"));

XML Bulk Loader processes the XML input data and prepares records for the appropriate tables in database. The logic in XML Bulk Loader determines when to generate a new record, what attribute values to copy into the fields of the record, and when the record is complete and ready to be sent to database for manipulation (insert, update or delete).

XML Bulk Loader handles name mapping properties, including column and table mappings (specified explicitly by using properties or implicitly through the default mapping).

Understanding record generation requires understanding the following concepts:

• Scope of a node

  Pertains to defining what a node is in the XML document and specifying when nodes come in and out of scope.

• Record generation process

  Pertains to knowing how records are generated as the nodes are processed (the "Record Generation Rule") and the life span of the record.

• Record and its subset

  Pertains to defining a record subset, specifying how subsets are generated, and describing how the location of key attributes or elements within the schema is important.

A node (an element or an attribute) enters "into scope" when XML Bulk Loader encounters it in the XML input data stream. For an element node, the start tag of the element brings the element in scope. For an attribute node, the attribute name brings the attribute in scope.

A node leaves scope when there is no more data for it: either at the end tag (in the case of an element node) or at the end of an attribute value (in the case of an attribute node).

IMPORTANT: In this model, because a record lives as long as the associated node is in scope, you must define all of the data that is associated with the record within the scope of the node.

When a node (element or attribute) enters into scope, there is a potential for generating a record from that node and inserting it into database. The record lives as long as the associated node is in scope. When the node goes out of scope, XML Bulk Loader does nothing.

For example, consider this DTD fragment:

<!ELEMENT customer (custOrder)*>
<!ATTLIST customer
    customerID ID #REQUIRED
    companyName CDATA #IMPLIED>

The DTD specifies a <customer> element with customerID and companyName attributes. The sql_map.properties maps the <customer> element to the Cust table:

customer=CUST
customerID=CUSTOMER_ID
companyName=COMPANY_NAME

Consider this fragment of an XML document:

<customer customerID=":1" companyName="xyz" />
<customer customerID=":2" companyName="abc" />
...

When XML Bulk Loader is provided with the DTD and name mappings that are described in the preceding paragraphs and XML data as input, it processes the nodes (elements and attributes) in the source data as follows:

• The start tag of the first <customer> element brings that element in scope. This node maps to the Cust table. Therefore, XML Bulk Loader generates a record for the Cust table.
  
  
• In the DTD, all attributes of the <customer> element map to columns of the Cust table. As these attributes enter into scope, XML Bulk Loader copies their values to the customer record that is already generated by the parent scope and sends it to database.
  
  
• When XML Bulk Loader reaches the end tag for the <customer> element, the element goes out of scope. This causes XML Bulk Loader to consider the record complete and do nothing for this mode.

XML Bulk Loader follows this process for each subsequent <customer> element.

When a node (element or attribute) enters into scope, there is a potential for generating a record from that node. The record lives as long as the associated node is in scope. When the node goes out of scope, XML Bulk Loader considers the generated record complete (with data) and sends it to database for updating.

When a node (element or attribute) enters into scope, there is a potential for generating a record from that node. The record lives as long as the associated node is in scope. When the node goes out of scope, XML Bulk Loader considers the generated record complete (with data) and sends it to database for deleting.

For example, consider this DTD fragment:

<!ELEMENT customer (custOrder)*>
<!ATTLIST customer
    customerID ID #REQUIRED>

The DTD specifies a <customer> element with customerID attribute. The sql_map.properties maps the <customer> element to the Cust table:

customer=CUST
customerID=CUSTOMER_ID

Consider this fragment of an XML document:

<customer customerID=":1" />
<customer customerID=":2" />
...

When XML Bulk Loader is provided with the DTD and name mappings that is described in the preceding paragraphs and XML data as input, it processes the nodes (elements and attributes) in the source data as follows:

• The start tag of the first <customer> element brings that element in scope. This node maps to the Cust table. Therefore, XML Bulk Loader generates a record for the Cust table.
  
  
• In the DTD, attribute of the <customer> element map to primary key of the Cust table. As this attribute enter into scope, XML Bulk Loader copies its value to the customer filter condition that is already generated by the parent scope.
  
  
• When XML Bulk Loader reaches the end tag for the <customer> element, the element goes out of scope. This causes XML Bulk Loader to consider the record complete and send it to database.

XML Bulk Loader follows this process for each subsequent <customer> element.

The subset term refers to the set of records that is generated on the "foreign" side of the relationship. In the following example, the CustOrder records are on the foreign side.

For example, a database contains these two tables:

• Cust (CustomerID, CompanyName, City)
  
  
• CustOrder (OrderID, CustomerID)

The CustomerID in the CustOrder table is a foreign key that refers to the CustomerID primary key in the Cust table.

Now, consider the XML structure as specified in the following DTD. This DTD uses containment to specify the relationship between the Cust and CustOrder tables (where related information is nested inside a parent element). Alternatively, if you want to keep the XML structures separate - like the tables of a database - you can use an ID to point to a corresponding structure that has an IDREF attribute.

<!ELEMENT customer (custOrder)*>
<!ATTLIST customer
    customerID ID #REQUIRED
    companyName CDATA #IMPLIED
    city CDATA #IMPLIED
    createdDate CDATA #REQUIRED>
    
<!ELEMENT custOrder EMPTY>
<!ATTLIST custOrder
    custOrderID ID #REQUIRED
    createdDate CDATA #REQUIRED
    customerIDREF IDREF #IMPLIED>

The sample XML data and the steps to create a working sample are given below.

• When a <customer> element node in the XML data file enters into scope, XML Bulk Loader generates a record for the Cust table. XML Bulk Loader then copies the necessary column values (CustomerID, CompanyName, City, and CreateDate) from the customerID, companyName, city, and the createDate  attributes.
  
  
• When an <custOrder> element node enters into scope, XML Bulk Loader generates a record for the CustOrder table. XML Bulk Loader copies the value of the custOrderID attribute to this record. The value required for the CustomerID column is obtained from the customerID attribute of the <customer> element. XML Bulk Loader uses the information that is specified in DTD to obtain the CustomerID foreign key value for this record, unless the CustomerID attribute was specified in the <custOrder> element. (The general rule is that if the child element explicitly specifies a value for the foreign key attribute, XML Bulk Loader uses that value and does not obtain the value from the parent element by using the specified containment). As this <custOrder> element node goes out of scope, XML Bulk Loader sends the record to database (update/delete mode) and then processes all the subsequent <custOrder> element nodes in the same manner.
  
  
• Finally, the <customer> element node goes out of scope. At that time, XML Bulk Loader sends the customer record to database (update/delete mode) or does nothing (create mode). XML Bulk Loader follows this process for all the subsequent customers in the XML data stream.

Here are two observations about the mapping schema:

• When the DTD satisfies the "containment" rule (for example, all data that is associated with the customer and the order is defined within the scope of the associated <customer> and <custOrder> element nodes), the bulk load succeeds.
  
  
• Pointing is more flexible than containment, but pointing relationships may only typically be navigated in one direction by the processor (create mode). In this case, the <customer> parent element should be specified before the <custOrder> child element. This means that in the input XML data file, the customerID attribute value is available as the foreign key value when the <custOrder> element enters into scope. The idea of specifying the key attributes first is also referred to as the "Key Ordering Rule."

  If you specify the <customer> parent element after the <custOrder> child element, the value is not available when the <custOrder> element enters into scope. When the <custOrder> start tag is read, the record for CustOrder table is considered complete and is inserted in the CustOrder table with a specified value for the CustomerID column but the parent record with this CustimerID key does not exist in Cust table yet, which is not the desired result.

XML provides a mechanism, the document type declaration, to define constraints on the logical structure and to support the use of predefined storage units. An XML document is valid if it has an associated document type declaration and if the document complies with the constraints expressed in it.

These declarations can be used within the DTD to specify XML to relational mapping. Name Mapping includes mapping between elements and attributes in the XML structure to tables (views) and columns in the databases.

You must bear in mind as you create these structures that there are usually many XML structures that may be used to represent the same relational database data. The techniques described in this chapter should allow you to optimize your documents for rapid processing and minimum document size. Using the techniques discussed in this chapter, you should be able to easily move information between your relational database and XML documents.

Here are the eleven rules that have been defined for the development of XML structures for data in a relational database:

• Rule 1: Choose the Data to Include. Based on the business requirement the XML document will be fulfilling, you decide which tables and columns from your relational database will need to be included in your documents.
• Rule 2: Create a Root Element. Create a root element for the document. You add the root element to your DTD, and declare any attributes of that element that are required to hold additional semantic information (such as routing information). Root element's names should describe their content.
• Rule 3: Model the Content Tables. Create an element in the DTD for each content table you have chosen to model. Declare these elements as EMPTY for now.
• Rule 4: Modeling Non-Foreign Key Columns. Create an attribute for each column you have chosen to include in your XML document (except foreign key columns). These attributes should appear in the !ATTLIST declaration of the element corresponding to the table in which they appear. Declare each of these attributes as CDATA, and declare it as #IMPLIED or #REQUIRED depending on whether the original column allows NULLS or not.
• Rule 5: Add ID Attributes to the Elements. Add an ID attribute to each of the elements you have created in your XML structure (with the exception of the root element). Use the element name followed by ID for the name of the new attribute, watching as always for name collisions. Declare the attribute as type ID, and #REQUIRED.
• Rule 6: Representing Lookup Tables. For each foreign key that you have chosen to include in your XML structures that references a lookup table:
  1. Create an attribute on the element representing the table in which the foreign key is found.
  2. Give the attribute the same name as the table referenced by the foreign key, and make it #REQUIRED if the foreign key does not allow NULLS or #IMPLIED otherwise.
  3. Make the attribute of the enumerated list type. The allowable values should be some human-readable form of the description column for all rows in the lookup table.
• Rule 7: Adding Element Content to Root elements. Add a child element or elements to the allowable content of the root element for each table that models the type of information you want to represent in your document.
• Rule 8: Adding Relationships through Containment. For each relationship you have defined, if the relationship is one-to-one or one-to-many in the direction it is being navigated, and no other relationship leads to the child within the selected subset, then add the child element as element content of the parent element with the appropriate cardinality.
• Rule 9: Adding Relationships using IDREF/IDREFS. Identify each relationship that is many-to-one in the direction you have defined it, or whose child is the child in more than one relationship you have defined. For each of these relationships, add an IDREF or IDREFS attribute to the element on the parent side of the relationship, which points to the ID of the element on the child side of the relationship.
• Rule 10: Add Missing Elements. For any element that is only pointed to in the structure created so far, add that element as allowable element content of the root element. Set the cardinality suffix of the element being added to *.
• Rule 11: Remove Unwanted ID Attributes. Remove ID attributes that are not referenced by IDREF or IDREFS attributes elsewhere in the XML structures.

When a root element enters into scope, there is a potential for opening a database connection from that element. The connection lives as long as the associated root element is in scope. When the root element goes out of scope, XML Bulk Loader considers the opened connection complete (with data) and closes it.

For example, consider this XML document fragment:

<ROOT driver="oracle.jdbc.driver.OracleDriver"
         url="jdbc:oracle:thin:@server:1521:ILMS27"
         user="scott"
         password="tiger"
         status="create">
         
     ... 
        
</ROOT>

The following optional attributes are used to specify the connection to database:

driver

The fully qualified driver class name. For example: oracle.jdbc.driver.OracleDriver.

url

The JDBC URL for the database connection. This URL typically begins with jdbc: and a short code name for the driver, followed by another colon. The rest of the URL depends on the type of driver you are using. For example, you might need to specify a host and port; alternatively, the name of a database file might suffice, or an ODBC DSN. If in doubt, refer to the documentation for the driver you are using.

user
password

The user name and password, if required by your database configuration.

The status required attribute is used to specify the type of bulk manipulating. It has three predefined values:

• create for inserting data into database
• update for updating data in database
• delete for deleting data from database

NOTE: If you omit one or more optional attributes, you'll be provided with the New Database Connection dialog box to enter the omitted information.

With Java Custom Extensions (JCE), you can generate the field values dynamically at run-time. JCE may be used in the value of element attributes. This is done by placing the JCE class name between "${" and "}" in the attribute value. For example, if there is a "com.synex.xml.ext.CurrentDate" class with the method getValue() that returns the current system date, then this could be used in an attribute like this: ${com.synex.xml.ext.CurrentDate}.

NOTE: The JCE class should implement CustomAction interface.

The following example class gets the current date:

public class CurrentDate implements CustomAction {

    public java.lang.String getValue() {
        return new java.sql.Timestamp(new java.util.Date().getTime()).toString();
    }
    
}

Then, consider this fragment of an XML document:

<custOrder custOrderID="custOrder:1"
           createdDate="${com.synex.xml.ext.CurrentDate}"/>
...

In the example above, the createDate attribute of <custOrder> is settled as the current system date during the bulk loading. Also, you can use JCE for generating of ID unique values or other application specific values.

If you want to create a primary key that should be used in the INSERT statement, then you must not specify an ID value (prefixed by a colon). For example:

<custOrder custOrderID="custOrder1"/>
<custOrder custOrderID="custOrder2"/>
...

IMPORTANT: XML Bulk Loader features automatic generation of keys for NUMERIC fields only.

Here is a short list of ways that might help your code run faster:

When inserting data from an XML document to the database, use the command:

   java com.synex.xml.sax.XMLParser -sqlm=<name-map-file> <xml-file>

For example, to insert data from the sample file TestCreate.xml to the database according the name map document sql_map.properties, use the command:

   java com.synex.xml.sax.XMLParser -sqlm=sql_map.properties TestCreate.xml

Like TestCreate, TestUpdate is a simple command-line application that updates existing data in the database according to a particular map.

To update existing data, use the command:

   java com.synex.xml.sax.XMLParser -sqlm=sql_map.properties TestUpdate.xml

TestDelete is a simple command-line application that deletes existing records from the database tables.

To run TestDelete, use the command:

   java com.synex.xml.sax.XMLParser -sqlm=sql_map.properties TestDelete.xml

Copyright (C) 2002 Sergey Yakovlev

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  
This is one of a set of classes that make up XML Bulk Loader,
a framework for bulk manipulating data contained in relational database.

The latest version of XML Bulk Loader is available at URL:
http://www.geocities.com/synexsoft/downloads/xblkld1.zip.

If you have questions about how to use XML Bulk Loader, the first thing you should do is read the documentation and look at the sample programs.

The following tables are needed to store the sample documents, as they are mapped with sql_map.properties and DTDs.