//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: XmlToTable.sqlj
//
// SAMPLE USAGE SCENARIO:Purchase order XML document contains detailed
// information about all the orders. It will also have the detail of the
// customer with each order.
//
// PROBLEM: The document has some redundant information as customer info
// and product info is repeated in each order for example
// Customer info is repeated for each order from same customer.
// Product info will be repeated for each order of same product from different customers.
//
// SOLUTION: The sample database has tables with both relational and XML data to remove
// this redundant information. These relational tables will be used to store
// the customer info and product info in the relational table having XML data
// and id value. Purchase order will be stored in another table and it will
// reference the customerId and productId to refer the customer and product
// info respectively.
//
// To achieve the above goal this sample will shred the data for purchase order XML
// document and insert it into the tables.
//
// The sample will follow the following steps
//
// 1. Get the relevant data in XML format from the purchase order XML document (use XMLQuery)
// 2. Shred the XML doc into the relational table. (Use XMLTable)
// 3. Select the relevant data from the table and insert into the target relational table.
//
// PREQUISITES : 1. Copy the file purchaseorder.xml from the xml/data directory to
// the current working directory
// 2. Create the pre-requisite tables by running the command:
// XmlToTableScrpt
// Alternatively,you can run the command:
// db2 -tvf XmlToTable_setup.db2
//
// EXECUTION : 1. Compile the sample using:
// bldsqlj XmlToTable <userid> <password> <server_name> <port_number> <db_name>
// 2. Run the sample as:
// java XmlToTable
// 3. Perform a clean up once the sample has been executed using:
// db2 -tvf XmlToTable_cleanup.db2
//
// SQL Statements USED:
// SELECT
// INSERT
//
// XML Functions USED:
// XMLCOLUMN
// XMLELEMENT
// XMLTABLE
// XMLDOCUMENT
// XMLATTRIBTES
// XMLCONCAT
// XQUERY
//
//
// Output will vary depending on the JDBC driver connectivity used.
//
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
// http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/
import java.lang.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
// Define the iterator to use in the functions
#sql iterator Positioned_Iterator1(int, String);
class XmlToTable
{
static int num_record_customer=0;
static int num_record_po=0;
public static void main(String argv[])
{
int rc=0;
String url = "jdbc:db2:sample";
DefaultContext ctx=null;
Connection con=null;
try
{
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
// connect to the 'sample' database
con = DriverManager.getConnection( url );
ctx= new DefaultContext(con);
// set the default context for the sample
DefaultContext.setDefaultContext(ctx);
System.out.println();
// call PO_shre method
PO_shred();
// display the content of the purchaseorder table
displayContent();
// clean up
cleanUp();
con.close();
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}// main
static void PO_shred()
{
String purchaseorder;
String data=null;
try
{
Statement stmt1 = DefaultContext.getDefaultContext().getConnection().createStatement();
// create table po
System.out.println("The purchaseorder table PO is created in the setup script \n" +
"XmlToTable_setup.db2 using the command \n" +
"'CREATE TABLE PO (purchaseorder XML)' \n");
purchaseorder = returnFileValues("purchaseorder.xml");
#sql { INSERT INTO PO(purchaseorder) values (:purchaseorder)};
// run the XQuery to find out the purchaseorder with status shipped
ResultSet rs=stmt1.executeQuery("XQUERY db2-fn:xmlcolumn('PO.PURCHASEORDER')/PurchaseOrders/PurchaseOrder[@Status='shipped']");
// iterate for all the rows, insert the data into the relational table
while(rs.next())
{
data=rs.getString(1);
// insert into customer table
System.out.println("Inserting into customer table ....");
#sql { INSERT INTO customer(CID,info,history) SELECT T.CustID,xmldocument
(XMLELEMENT(NAME "customerinfo",XMLATTRIBUTES (T.CustID as "Cid"),
XMLCONCAT( XMLELEMENT(NAME "name", T.Name ), T.Addr,
XMLELEMENT(NAME "phone", XMLATTRIBUTES(T.type as "type"), T.Phone)
))), xmldocument(T.History)
FROM XMLTABLE( '$d/PurchaseOrder' PASSING cast(:data as XML) AS "d"
COLUMNS CustID BIGINT PATH '@CustId',
Addr XML PATH './Address',
Name VARCHAR(20) PATH './name',
Country VARCHAR(20) PATH './Address/@country',
Phone VARCHAR(20) PATH './phone',
Type VARCHAR(20) PATH './phone/@type',
History XML PATH './History') as T
WHERE T.CustID NOT IN (SELECT CID FROM customer) };
num_record_customer++;
System.out.println("Inserting into purchaseorder table .....\n");
#sql { INSERT INTO purchaseOrder(poid, orderdate, custid,status, porder, comments)
SELECT poid, orderdate, custid, status,xmldocument(XMLELEMENT(NAME "PurchaseOrder",
XMLATTRIBUTES(T.Poid as "PoNum", T.OrderDate as "OrderDate",
T.Status as "Status"),
T.itemlist)), comment
FROM XMLTable ('$d/PurchaseOrder' PASSING cast(:data as XML) as "d"
COLUMNS poid BIGINT PATH '@PoNum',
orderdate date PATH '@OrderDate',
CustID BIGINT PATH '@CustId',
status varchar(10) PATH '@Status',
itemlist XML PATH './itemlist',
comment varchar(1024) PATH './comments') as T };
num_record_po++;
}
// close the resultset
rs.close();
System.out.println("Number of rows inserted in customer table: " + num_record_customer);
System.out.println("Number of rows inserted in purchaseorder table: " + num_record_po);
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e)
{
}
System.exit(1);
}
catch(Exception e)
{}
}
public static String returnFileValues(String fileName)
{
String record = null;
try
{
FileReader fr = new FileReader(fileName);
BufferedReader br = new BufferedReader(fr);
record = new String();
record = br.readLine();
String descReturn=record;
while ((record = br.readLine()) != null)
descReturn=descReturn+record;
return descReturn;
}
catch (IOException e)
{
// catch possible io errors from readLine()
System.out.println(" file " + fileName + "doesn't exist");
System.out.println(" Can not continue with insert, please verify "+fileName+" and try again.");
System.out.println(" Quitting program!");
System.out.println();
System.exit(-1);
}
return null;
}// returnFileValues
static void displayContent()
{
String data=null;
int cid = 0;
try
{
Positioned_Iterator1 custIter=null;
System.out.println("CONTENT OF CUSTOMER TABLE");
#sql custIter = { SELECT CID, INFO FROM CUSTOMER };
while (true)
{
// fetch the cursor
#sql {FETCH :custIter INTO :cid, :data};
if (custIter.endFetch())
{
break;
}
// print the result
System.out.println();
System.out.println("CUSTOMER "+cid);
System.out.println();
System.out.println("INFO " +data);
}
// close the cursor
custIter.close();
System.out.println("CONTENT OF PURCHASEORDER TABLE");
#sql custIter = { SELECT poid, porder FROM PURCHASEORDER };
while (true)
{
// fetch the cursor
#sql {FETCH :custIter INTO :cid, :data};
if (custIter.endFetch())
{
break;
}
// print the result
System.out.println();
System.out.println("POID "+cid);
System.out.println();
System.out.println("PORDER "+ data);
}
// close the cursor
custIter.close();
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e) {}
System.exit(1);
}
} // displayContent
static void cleanUp()
{
try
{
#sql { DELETE FROM PURCHASEORDER WHERE POID IN (110,111) };
#sql { DELETE FROM CUSTOMER WHERE CID IN (10,11) };
}
catch (SQLException sqle)
{
System.out.println("Error Msg: "+ sqle.getMessage());
System.out.println("SQLState: "+sqle.getSQLState());
System.out.println("SQLError: "+sqle.getErrorCode());
System.out.println("Rollback the transaction and quit the program");
System.out.println();
try { DefaultContext.getDefaultContext().getConnection().rollback(); }
catch (Exception e) {}
System.exit(1);
}
}
} // XmlToTable