Developer

More Sybase XML basics

Java and XML are the perfect tools for working with Sybase data. Learn how easy it is to create XML result sets with Sybase ASE.


The ResultSetXml class is a generic subclass of the JXml class. This class is designed to create an XML result document from any valid SQL statement on any SQL table. It also provides methods for validation, access, and updating the XML Result set document. Here we’ll create XML result sets with the ResultSetXml class.

The source code and Javadoc for the ResultSetXml class are located in the following Sybase Server directory (depending on your platform).
$SYBASE/ASE-12_5/sample/JavaSql (UNIX)
%SYBASE%\ASE-12_5\sample\JavaSql (Windows NT)

Creating an XML result set
For this example we’ll use the tables we created and populated (Listing A) in the last article.

The SQL in Listing B creates the tables to use with the sample data. It contains the standard SQL to populate the Orders tables.

The example in Listing C builds a SQL statement from the client to query the server tables we created in Listing A. Then it uses the ResultSetXml constructor method to create a ResultSetXml object. We then create the OrderResultSet.xml file, which we’ll explore next. In the above example, we’d use jdbc:default:connection (or just leave it blank) for the login information if we were executing the snippet on the server.

The ResultSetXml document we created contains both ResultSetMetaData and ResultSetData sections. This ResultSetXml document follows the following format:
<?xml version="1.0"?>
 <!DOCTYPE ResultSet SYSTEM 'ResultSet.dtd'>
 <ResultSet>
<ResultSetMetaData>
...
 </ResultSetMetaData>
<ResultSetData>
...
 </ResultSetData>
</ResultSet>

The full document, which can be seen in Listing D, contains the ResultSetXml document (in the file OrderResultSet.xml) created by our example.

The first section of a ResultSetXml document (Listing D), the ResultMetaData, contains the information that is not specific for any particular column, followed by the ColumnMetaData information. The ColumnMetaData attributes map directly to the JDBC methods of the ResultMetaData class. The next section, the ResultSetData information, contains the actual Row elements, each of which contains the Column attribute names and data.

The ResultSetXml DTD
The ResultSet.dtd DTD (Document Type Definition) for the ResultSetXml document in Listing E describes our ResultSetXml document.

Here’s a quick explanation of the DTD in Listing E:
  • ·        The <!ELEMENT ResultSet (ResultSetMetaData, ResultSetData)> states that the ResultSetXml document has an outermost ResultSet node that contains two embedded nodes, ResultSetMetaData and ResultSetData.
  • ·        The <!ELEMENT ResultSetMetaData (ColumnMetaData)+> states that the ResultSetMetaData section has one or more ColumnMetaData nodes (+ means one or more and ? means zero or more).
  • ·        The <!ATTLIST ResultSetMetaData getColumnCount CDATA &num;IMPLIED> means that the ResultSetMetaData section has an attribute of GetColumnCount preceding the ColumnMetaData node(s).

Storage options
We have the same options to store the ResultSetXml.xml that we examined in the previous article on the OrderXml custom class. Element, document, and hybrid storage techniques all apply. We’ll explore those capabilities in greater depth in our next article.

Where do we go from here?
In our last article we learned about the XML storage and retrieval capabilities available in Sybase ASE (Adaptive Server Enterprise) through ASE’s ability to use Java custom classes. Here we’ve explored the ResultSetXml class and both the DTD and the XML document that it describes, and we’ve examined a snippet of code that takes a standard SQL SELECT statement and creates a ResultSetXml document on both the client and the server. It’s probably apparent by now that the ResultSetXml document can be used to describe any result set, unlike the OrderXml class in our last article, but it can’t be used to create nested nodes. That’s only possible by creating a custom Java class to be used with a strict set of data. A custom class can be used to construct more than one document form. It’s yet another reason why having a Java Virtual Machine on the server is so powerful.

In the next article, we’ll explore translating the ResultSetXml in both the client and the server and accessing the columns of a ResultSetXml document while stored, using the document storage method, as a column on a SQL table. We’ll also perform a quantified comparison with the document storage method in much the same way one would use a WHERE clause in a normal SQL statement.

Editor's Picks