Developer

Store and retrieve XML data with custom Java classes in Sybase

XML support has evolved into a must for any database system, and Sybase has everything covered in its most recent version. Take a look at how to store XML data for use with SQL-reliant legacy systems.


While no one is abandoning the high-performance storage and SQL query engines that come with proprietary enterprise database products, the ability to openly interface between entities in a HTTP-friendly text format is making XML very popular. In this article, we’ll look at Sybase Adaptive Server Enterprise (ASE) 12.5 and its different methods of storing and processing XML data. We’ll also discuss the sample OrderXml Java custom class (a subclass of the standard JXml class) that comes with ASE 12.5.

Finding the source code
The source code and Javadoc for the OrderXml class used in this article is located in following Sybase server directories (depending on your platform):
UNIX: $SYBASE/ASE-12_5/sample/JavaSql
Windows NT: %SYBASE%\ASE-12_5\sample\JavaSql


Integration of XML into Sybase ASE
While most major database vendors have made important strides to implement XML via basic extensions to SQL statements and server-based templates, Sybase has taken a far more serious approach to XML in its flagship product. Sybase's ASE has a powerful server-based Java VM.

Among ASE’s most powerful XML processing capabilities is the ability to use your own custom Java classes. You can use these classes’ methods to extract data from XML documents and store it as normal SQL datatypes, or even use the methods as datatypes themselves for columns on SQL tables. These Java custom class datatypes can have methods to validate, extract, and update/add/delete elements from the stored XML documents as needed. These classes can even create and populate SQL tables with separate elements extracted from the XML document for use by non-XML aware legacy applications.

XML storage
Sybase ASE can store XML data in three ways: element storage, document storage, and hybrid storage (a combination of both the element and document methods).

For the purposes of this discussion we’ll use the following sample OrderXml document:
<?xml version="1.0"?><Order>
<Date>1999/07/04</Date>
<CustomerId>123</CustomerId>
<CustomerName>Acme Alpha</CustomerName>
<Item>
<ItemId> 987</ItemId>
<ItemName>Coupler</ItemName>
<Quantity>5</Quantity>
</Item>
<Item>
<ItemId>654</ItemId>
<ItemName>Connector</ItemName>
<Quantity unit="12">3</Quantity>
</Item>
<Item>
<ItemId>579</ItemId>
<ItemName>Clasp</ItemName>
<Quantity>1</Quantity>
</Item>
</Order>


The following SQL creates the tables to use with the sample data:
create table orders
(customer_id varchar(5) not null,
order_date datetime not null,
item_id varchar(5) not null,
quantity int not null,
unit smallint default 1)
create table customers
(customer_id varchar(5) not null unique,
customer_name varchar(50) not null)
create table items
(item_id varchar(5) unique,
item_name varchar(20))


The following would be the standard SQL to populate the Orders tables.
insert into customers values("123", "Acme Alpha")

insert into orders values ("123", "2002/05/07", "987", 5, 1)
insert into orders values ("123", "2002/05/07", "654", 3, 12)
insert into orders values ("123", "2002/05/07", "579", 1, 1)

insert into items values ("987", "Widget")
insert into items values ("654", "Medium connector")
insert into items values ("579", "Type 3 clasp")


The following SQL selects the data from the database once it has been stored.
select order_date as Date, c.customer_id as CustomerId,
customer_name as CustomerName,
o.item_id as ItemId, i.item_name as ItemName,
quantity as Quantity, o.unit as unit
from customers c, orders o, items I
where c.customer_id=o.customer_id and
o.item_id=i.item_id


The output looks something like what you see in Table A.
Table A
Date CustomerID CustomerName ItemId ItemName Quantity Unit
July 4 1999 123 Acme Alpha 987 Coupler 5 1
July 4 1999 123 Acme Alpha 654 Connector 3 12
July 4 1999 123 Acme Alpha 579 Clasp 1 1
SQL output

Next, let’s look at examples of storage methods and techniques to create a current_orders table to use in place of the orders table created and populated above.

Element Storage
Element Storage is the method by which you extract elements from XML documents and store them in the standard SQL column and row format. This allows SQL queries, from legacy applications that are not XML-aware, to access the data in the standard SQL fashion. The drawback to this approach is that the data elements need to be extracted from the XML document before being stored; if the data needs to be composed again in XML format, this extra step may have substantial overhead.

Now let’s take a look at creating a current_orders table for the elements, extracting those elements from an OrderXml document, and placing them inside a SQL table using the methods of an OrderXml Java custom class.

The OrderXml class has a constructor event that validates that a string contains a valid OrderXml document and returns an object of type OrderXml. It also has an order2Sql method that takes the elements of an OrderXml document and places them in the table created by the OrderXml method createOrderTable.
// Create the OrderXml object from the XML document
Xml.order.OrderXml ox = new xml.order.OrderXml( doc )

// Create the current_orders table on the server
xml.order.OrderXml.createOrderTable( “current_orders”, “test_server:4000?user=sa” )

// Populate the current_orders table with the elements found in the OrderXml object
ox.Order2Sql( “current_orders”, “test_server:4000?user=sa”)


We now have an SQL table named current_orders on the server (containing the extracted elements of our Order.xml document) that matches the data and structure in the orders table we created earlier.

If we need to compose an Orders document from the current_orders table we have a constructor method of OrderXml that takes the order_date, customer_id and login information as arguments and then uses JDBC for creating a SQL query. It composes an OrderXml document with the requested information and returns it as an OrderXml object.
// Compose a XML document from a SQL table
xml.order.OrderXml ox = new OrderXml( “990704”, “123”, “test_server:4000?user=sa” )


Document storage
Document storage is the method by which you create a single column in a table to hold the XML document. The column may be text, generic Java class, or custom Java class (designed for use with a specific XML document type stored in the database). While regular SQL may be used to INSERT, UPDATE, or DELETE the document in its entirety, Java is required to reference or update the elements of the document.

This method is preferred where XML is always the required database output format. The overhead of extracting the elements from or composing them to an XML document is unnecessary.

This table is designed to hold the XML document as a custom Java class.
create table order_docs (id char(10) unique, order_doc xml.order.OrderXml)

The column definition of order_doc uses xml.order.OrderXml, which is actually a custom Java class. ASE comes with this example of a custom class, which shows just how versatile having a Java VM on a database server can be.

The OrderXml class has a constructor that validates a string passed to it as a valid XML Order document. An OrderXml object is then returned; this object is suitable for storage in the column defined in our table.
Xml.order.OrderXml ox = new xml.order.OrderXml(doc);

Hybrid storage
Hybrid storage is the method whereby XML is stored as a document (not disassembled), but one or more columns are extracted from the XML document and stored as regular SQL columns. This approach could be used where XML is the data source and is stored in pieces as it comes in. For example, this might be a single order as it came in from an outside sales system and would need to be queried by customer_id at a later time.
create table order_xmldocs
(id char(10) unique,
customer_id varchar(5) not null,
order_date datetime not null,
order_doc xml.order.OrderXml)


The power of customization
The ability to create customized Java classes and then use them to declare Java datatypes and SQL columns on a database server is a very powerful capability. Combined with the flexibility to store XML data in its native format as well as extract the elements for normal SQL table storage, this allows the flexibility necessary for enterprise, e-commerce, and B2B solutions that today’s marketplace demands.

Editor's Picks

Free Newsletters, In your Inbox