Using Rowsets to Display Data - Java Database Programming Bible - page 456

Chapter 2 discusses the design of a database intended to implement an XML-based billing standard
known as LEDES 2000 — the Legal Electronic Data Exchange Standard. Since the database structure
is quite complex, it is not discussed here. Suffice it to say that the query required to retrieve information
about billable items to be inserted in an invoice is defined in the stored procedure called
Itemise_Fees , shown in Listing 18-9 .
Listing 18-9: Stored procedure to retrieve billable item data
CREATE PROCEDURE ITEMISE_FEES @Matter_Id INT AS
SELECT bi.date AS charge_date, bi.tk_id,
bi.description AS charge_desc, bi.task_code AS acca_task,
bi.activity_code AS acca_activity, rc.value AS charge_type,
bi.units, fc.rate, bi.units * fc.rate AS base_amount,
dc.value AS discount_type, br.discount AS discount_percent,
(bi.units * fc.rate) * (1 - br.discount / 100)
AS total_amount
FROM Billings b, Billable_Items bi, Billing_Rates br,
Discount_Codes dc, Timekeeper tk, Fee_Codes fc,
Rate_Codes rc
WHERE b.matter_id = bi.matter_id AND
dc.code = br.discount_type AND bi.rate_code = br.id AND
fc.code = tk.rate_code AND tk.id = bi.tk_id AND
rc.code = bi.rate_code AND bi.invoice_number IS NULL AND
b.status = 1 AND bi.matter_id = @Matter_Id;
Listing 18-10 illustrates the use of the stored procedure to retrieve billable items for Matter_Id 10001. As
you can see, the XML is written to a file called fees.xml .
Listing 18-10: Writing XML with a WebRowSet
package JavaDatabaseBible.ch18;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import sun.jdbc.rowset.*;
public class WebRowSetExample{
public static void main(String[] argv){
String url = "jdbc:odbc:LEDES";
String login = "jod";
String password = "jod";
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");