Frequently Asked Questions (FAQs): XML-SQL Utility (XSU)

What Schema Structure to Use With XSU to Store XML?

Question

I have the following XML in my customer.xml file:

<ROWSET>
 <ROW num="1">
  <CUSTOMER>
   <CUSTOMERID>1044</CUSTOMERID>
   <FIRSTNAME>Paul</FIRSTNAME>
   <LASTNAME>Astoria</LASTNAME>
   <HOMEADDRESS>
    <STREET>123 Cherry Lane</STREET>
    <CITY>SF</CITY>
    <STATE>CA</STATE>
    <ZIP>94132</ZIP>
   </HOMEADDRESS>
  </CUSTOMER>
 </ROW>
</ROWSET>

What database schema structure should I use to store this xml with XSU?

Answer

Since your example is more than one level deep (i.e. has a nested structure), you should use an object-relational schema. The XML above will canonically map to such a schema. An appropriate db. schema would be the following:

create type address_type as object
 (
 street varchar2(40),
 city varchar2(20),
 state varchar2(10),
 zip varchar2(10)
 );
 /
 create type customer_type as object
 (
customerid number(10),
firstname varchar2(20),
lastname varchar2(20),
homeaddress address_type
 );
/
create table customer_tab ( customer customer_type);

In the case you wanted to load customer.xml via the XSU into a relational schema, you could still do it by creating objects in views on top of your relational schema.

For example, you would have a relational table which would contain all the information:

create table cust_tab
 ( customerid number(10), 
   firstname varchar2(20), 
   lastname varchar2(20), 
   state varchar2(40),
   city varchar2(20), 
   state varchar2(20), 
   zip varchar2(20)
 );

Then you would create a customer view which contains a customer object on top of it, as in:

create view customer_view as
select customer_type(customerid, firstname, lastname,
address_type(state,street,city,zip))
from cust_tab;

Finally, you could flatten your XML using XSLT and then insert it directly into your relational schema. This is the least recommended option.

Storing XML Data Across Tables

Question

Can XML- SQL Utility store XML data across tables?

Answer

Currently XML-SQL Utility (XSU) can only store to a single table. It maps a canonical representation of an XML document into any table/view. But of course there is a way to store XML with the XSU across table. One can do this using XSLT to transform any document into multiple documents and insert them separately. Another way is to define views over multiple tables (object views if needed) and then do the inserts ... into the view. If the view is inherently non-updatable (because of complex joins, ...), then one can use INSTEAD-OF triggers over the views to do the inserts.

Using XML-SQL Utility to Load XML Stored in Attributes

Question

I would like to use the XML-SQL Utility to load XML where some of the data is stored in attributes; yet, the XML-SQL Utility seems to ignore the XML attributes. What can I do?

Answer

Unfortunately, for now you will have to use XSLT to transform your XML document (i.e. change your attributes into elements). The XML-SQL Utility does assume canonical mapping from XML to a db. schema. This takes away a bit from the flexibility, forcing the user to sometimes resort to XSLT, but at the same time, in the common case, it doesn't burden the user with having to specify a mapping.

XML-SQL Utility is Case Sensitive: Use ignoreCase or...

Question

I am trying to insert the following XML document (dual.xml):

<ROWSET>
   <row>
      <DUMMY>X</DUMMY>
   </row>
</ROWSET>

Into the table "dual" using the command line front end of the XSU, like in:

java OracleXML putxml -filename dual.xml dual

and I get the following error:

oracle.xml.sql.OracleXMLSQLException: No rows to modify -- the row enclosing tag 
missing.  Specify the correct row enclosing tag.

Answer

By default the XML SQL Utility is case sensitive, so it looks for the record separator tag which by default is "ROW"; yet, all it can find is "row". Another related common mistake is to case mismatch one of the element tags. For example if in dual.xml the tag "DUMMY" was actually "dummy" than the XML SQL Utility would also raise an error complaining that if couldn't find a matching column in the table "dual". So user has two options -- use the correct case or use the "ignoreCase" feature.

Generating Database Schema from a DTD

Question

Given a DTD, will the XML SQL Utility generate the database schema?

Answer

No. Due to a number of shortcomings of the DTD, this functionality is not available. Once XML Schema standard is finalized this functionality will become feasible.

Using XML-SQL Utility Command Line

Question

I am using the XML SQL Utility's command line front end, and I am passing a connect string but I get a TNS error back. Can you provide examples of a thin driver connect string and an OCI8 driver connect string?

Answer

An example of an JDBC thin driver connect string is: "jdbc:oracle:thin:<user>/<password>@<hostname>:<port number>:<DB SID>"; furthermore, the db. has to have a active TCP/IP listener. A valid OCI8 connect string would be: "jdbc:oracle:oci8:<user>/<password>@<hostname>".

Does XML-SQL Utility Commit After INSERT, DELETE, UPDATE?

Question

Does XML SQL Utility commit after it's done inserting/deleting/updating? What happens if an error occurs.

Answer

By default the XML SQL Utility executes a number of insert (or del or update) statements at a time. The number of statements batch together and executed at the same time can be overridden using the "setBatchSize" feature.

By default the XML SQL Utility does no explicit commits. If the autocommit is on (default for the JDBC connection) then after each batch of statement executions a commit happens. The user can override this by turning autocommit off and then specifying after how many statement executions should a commit occur which can be done using the "setCommitBatch" feature.

Finally, what happens if an error occurs... Well, the XSU rolls back to either the state the target table was before the particular call to the XSU, or the state right after the last commit made during the current call to the XSU.