Decomposing data from an XML Order into SQL

In this section, you extract elements from an XML Order document and store them in the rows and columns of the SQL Orders tables. The examples illustrate this procedure in both server and client environments.

You decompose the elements using the Java method order2Sql( ) of the OrderXml class. Assume that xmlOrder is a Java variable of type OrderXml:

xmlOrder.order2Sql(“orders_received”, “antibes:4000?user=DBA&password=SQL”);

The order2Sql( ) call extracts the elements of the XML Order document contained in variable xmlOrder, and then uses JDBC operations to insert that data into the SQL table orders_received. You can call this method on the client or on Sybase IQ:

• Invoked from the client, order2Sql( ) extracts the elements of the XML Order document on the client, uses jConnect to connect to the IQ server, then uses Transact-SQL insert to place the extracted data into the table.

• Invoked from the server, order2Sql( ) extracts the elements of the XML Order document in the IQ server, uses the native JDBC driver to connect to the current IQ server, then uses Transact-SQL insert to place the extracted data into the table.

Invoked from the client, the main( ) method of the Order2SqlClient class creates a table named orders_received with columns suitable for Order data. main( ) then extracts the elements of the XML Order contained in the file Order.xml into rows and columns of orders_received. It performs these actions with calls to the static method OrderXml.createOrderTable( ) and the instance method order2Sql( ).

import jcs.util.*;
import jcs.xml.orders.*;
import java.io.*;
import java.sql.*;
import java.util.*;

public class Order2SqlClient {
 	public static void main (String args[]) {
 		try{ 
 			String xmlOrder =
 				FileUtil.file2String("order.xml");
 			OrderXml.createOrderTable("orders_received",
 				"antibes:4000?user=DBA&password=SQL");
 			xmlOrder.order2Sql("orders_received",
 				"antibes:4000?user=DBA&password=SQL");
 		} catch (Exception e) {
 		System.out.println("Exception:");
 		e.printStackTrace();
 		}
 	}
 }

Invoked from the server, the following SQL script invokes the OrderXml constructor to generate an XML Order document from the SQL tables, and then invokes the method OX.sql2Order( ), which extracts the Order data from the generated XML and inserts it into the orders_received table.

create variable xmlorder jcs.xml.orders.OrderXml
set xmlorder = new jcs.xml.orders.OrderXml('19990704', '123',
 	'external', '') 
select xmlorder>>order2Sql('orders_received', '')