Decomposing the XML ResultSet to SQL data

In this section, you decompose an existing ResultSet document to SQL data.

In the section “Decomposing data from an XML Order into SQL”, you invoke the order2Sql( ) method of the OrderXml class to decompose an XML Order document into SQL data. order2Sql( ) directly inserts the extracted data into a SQL table.

In this example, the toSqlScript( ) method of the ResultSetXml class decomposes an XML ResultSet document into SQL data. Instead of directly inserting extracted data into a SQL table, however, toSqlScript( ) returns a SQL script with generated insert statements.

The two approaches are equivalent.

Decomposing the XML ResultSet document on the client

The main( ) method of ResultSetXml is executed in a client environment. It copies the file OrderResultSet.xml, constructs a ResultSetXml object containing the contents of that file, and invokes the toSqlScript( ) method of that object to generate a SQL script that recreates the data of the result set. The method stores the SQL script in the file order-resultset-copy.sql.

import java.io.*;
import jcs.util.*;
public class ResultSet2Sql{
    public static void main (String[] args) {
       try{
          String xml = FileUtil.file2String("OrderResultSet.xml");
          jcs.xml.resultset.ResultSetXml rsx  
 			= new jcs.xml.resultset.ResultSetXml(xml);
          String sqlScript 
 			= rsx.toSqlScript("orderresultset_copy", "col_",
 				"both", "no");
          FileUtil.string2File("order-resultset-copy.sql",
 			sqlScript);
          jcs.util.ExecSql.statement(sqlScript,
 			“antibes:4000?user=DBA&password=SQL”);
       } catch (Exception e) {
          System.out.println("Exception:");
          e.printStackTrace();
       }
    }
 }

The following is the SQL script generated by ResultSet2Sql.

set quoted_identifier on 
 create table orderresultset_copy ( 
    Date datetime not null , 
    CustomerId varchar (5) not null , 
    CustomerName varchar (50) not null , 
    ItemId varchar (5) not null , 
    ItemName varchar (20) not null , 
    Quantity integer not null , 
    unit smallint not null 
 )

insert into orderresultset_copy values (
    '1999-07-04 00:00:00.0',   '123', 
 	'Acme Alpha',    '987', 'Widget', 5,  1 )
 insert into orderresultset_copy values (
    '1999-07-04 00:00:00.0',  '123',  
 	'Acme Alpha',  '654',  
 	'Medium connecter',  3,  12 )
 insert into orderresultset_copy values (
 	'1999-07-04 00:00:00.0',  '123',  
 	'Acme Alpha',  '579',  'Type 3 clasp',  1,  1 )

The SQL script includes the set quoted_identifier on command for those cases in which the generated SQL uses quoted identifiers.

Decomposing the XML ResultSet document in Sybase IQ

The following SQL script invokes the toSqlScript( ) method in Sybase IQ and then creates and populates a table with a copy of the result set data.

create variable rsx jcs.xml.resultset.ResultSetXml;

set rsx = new jcs.xml.resultset.ResultSetXml
 	('select 1 as ''a'', 2 as ''b'', 3 ', 'none', 'yes', 'external', '');

create variable script java.lang.String;

set script = ( select rsx>>toSqlScript('resultset_copy', 'column_', 'both', 'no'));

select jcs.util.ExecSql.statement(script, '');