Sybase’s aggressive approach to XML integration makes it easy to store XML in a Sybase database. We’ll explore using the ResultSetXml class to store a ResultSet document with both the element and hybrid methods. We’ll also manipulate data with the hybrid storage method.

Did you miss something?

Check out parts one and two of this series, which cover the content of the ResultSet.xml document, its metadata and data, and how to generate such documents from a standard SQL statement:
“Store and retrieve XML data with custom Java classes in Sybase”
“More Sybase XML basics”

Storing the ResultSet.xml using the element method
The following examples take the OrderResultSet.xml document and generate a SQL script (order-result) to create the desired table, and then populate it with the same Order data we’ve been using all along in this series of articles.

On the client
On the client, we can create a ResultSetXml document stored as a file and create a standard SQL table, as shown in Listing A.

On the server
On the server, we can take a ResultSetXml document stored in a column on a table using the hybrid storage method and create a standard SQL table, as shown in Listing B.

The toSqlScript() method
The SqlScript method creates and populates the specified table. It takes three parameters:

  • ·        ResultTableName (string)—The name of the table for the CREATE and INSERT statements.
  • ·        ColumnPrefix (string)—A prefix to use before column names as specified by the ResultSet XML document.
  • ·        GoOption (string)—Whether or not the resulting SQL script will include the Go commands required in isql. If the script is to be executed with the util.ExecSql.statement() method (via JDBC), you would specify “no” for this option. If you wish to submit this SQL script through isql, you would specify “yes.”

The SQL script
The resulting SQL script looks like Listing C.

After the call to toSqlScript() we execute the script with the util.ExecSql.statement() method. If we’re executing in the client, we have the additional option of saving the SQL script to a file with the FileUtil.string2File() method. For obvious reasons, the io and gui classes are not supported by the JVM on ASE.

Hybrid storage method: Accessing column data
Although performance considerations dictate that we use the Element Storage technique if we need any serious access to XML document data for modifications, there are times when we need to modify the stored XML document. We’ll look at examples of modifying a stored XML document on the client and the server.

On the client
In this example, we get the original values and display them to the user. Then we modify the values and get and show the modified values as proof of the success of our process. The code is displayed in Listing D.

If we examine the Order-updated.xml file we saved on the client (via Listing D), we see that we’ve changed the item “Connector” to the item “Flange” as Listing E shows.

On the server
On the server, we use the capabilities of the ASE Java VM to access the XML column directly with SQL statements (see Listing F).

Quantified ResultSetXml comparisons
We’ll now cover quantified comparisons in stored documents. First, let’s massage our data a little so we have a larger data set to work with and can show the different methods and results of quantified comparisons (Listing G).

Next, we do a select on the table to show the three items in each of the three order rows as follows:
select id,
rs_doc>>getColumn(0, “ItemId”) as “Item 1”,
rs_doc>>getColumn(1, “ItemId”) as “Item 2”,
rs_doc>>getColumn(2, “ItemId”) as “Item 3”
from resultset_docs

And we get the normal SQL result set displayed in Table A.
Table A

Id Item 1 Item 2 Item 3
== ===== ===== =====
1 100 110 120
2 987 654 579
3 987 654 999

Normal SQL result

The two methods of quantified comparison available to us (as methods of the ResultSetXml class) are the allString() method, used to determine if all rows in the result set are true, and the someString() method, used to determine if any row in the result set is true.

Also, take note of the use of the booleanValue() in the quantified WHERE clauses to accommodate the fact that java.lang.boolean is not implicitly convertible with the SQL integer type (which uses 0 for False and 1 for True).
// show id and boolean value for all rows using quantifier in select
// clause where 3rd column (ItemId) is less than 200 is true
select id,
rs_doc>>allString(3, “<“, “200”) as “ALL test”
from resultset_docs

Table B shows the result set from the previous select statement.
Table B

ID ALL test
== =====
1 true
2 false
3 false

Result set
// show id and boolean value for all rows using quantifier in select clause
// where 3rd column (ItemId) is greater than or equal to 999 is true
select id,
rs_doc>>someString(3, “>=”, “999”) as “SOME test”
from resultset_docs

Table C shows the result set from the previous select statement.
Table C

ID SOME test
== =========
1 false
2 false
3 true

Result set
// show id for only qualifying rows using quantifier in where
// clause where 3rd column (ItemId) is less than 200
select id as “id for ALL test” from resultset_docs
where rs_doc>>allString(3, “<“, “200”)>>booleanValue() = 1

Result set
id for ALL test
===========
1
 
 
// show id for only qualifying rows using quantifier in where
// clause where 3rd column (ItemId) greater than or equal to 999
select id as “id for SOME test” from resultset_docs
where rs_doc>>someString(3, “>=”, “999”)>>booleanValue() = 1
 

Result set
id for SOME test
=============
3

In our series, we’ve covered the basics, and then some, of using XML. We’ve seen the different methods available to us in assembling, disassembling, storing, selecting, and manipulating XML data on both the client and server. While vendor implementations of XML may vary considerably, Sybase’s ASE has a robust and aggressive approach.

Keep your XML skills sharp

Application developers will walk away with useful XML information and coding that they can use on the job. Delivered each Wednesday, our XML tips will help subscribers stay ahead of the technology curve. Sign up now!