XSU

[rajib_dt]

Hi
My requirement is to get XML document from a given query through java API -Very simple But I am facing following problem .Can U please help me out.
In my case Oracle 8.0 running in UNIX and I want connect to through java from client. I have downloaded XSU package.Got the xmlparserv2.jar & xsu12.jar .I set them in the classpath and trying to run a very simple program useing OracleXMLQuery object . Though it is compiling while running it is giving following Error. For your I have printed the classpath & pested out here for your referance.

D:\myXml>java XSUTry
Exception in thread "main" java.lang.NoClassDefFoundError: oracle/sql/Datum
at oracle.xml.sql.query.OracleXMLQuery.(OracleXMLQuery.java:125)
at XSUTry.main(XSUTry.java:46)

D:\myXml>set CLASSPATH
CLASSPATH=D:\myXml\;D:\myXml\OracleXSU\lib\xsu12.jar;D:\myXml\OracleXSU\lib\xmlparserv2.jar;;.;D:\or ac
le.jar;c:\jdk1.2.2\lib\tools.jar;c:\SystemComponents\sm\vendortool\jdbc;d:\weblogic\classes\;d:\webl ogic\lib\weblogicaux.jar;d:\weblogic\lib\unpacked_jars\javax_ejb.zip;d:\weblogic\lib\unpacked_jars\j avax_jts.zip;D:\Apache-Group\PoolMan\lib\;D:\Apache-Gro
up\PoolMan\lib\PoolMan.jar;D:\ShoppingCart\codexml\;


Is it required some additional package or whether I have to set anything in the sever side . If any body can help me -Please help me out. What its giving as "oracle/sql/Datum" .

Thanx in advance .

[Mahi]

Hope this will be helpful
Mahi

Subject: How to store XML data across multiple tables using XML SQL


Article-ID:
Circulation: REVIEWED (EXTERNAL)
Folder: languages.XML
Topic: Sample Code Articles
Title: How to store XML data across multiple tables using XML SQL
Utility
Document-Type: SCRIPT
Impact: MEDIUM
Skill-Level: NOVICE
Server-Version: 8I
Updated-Date: 11-JAN-2001 06:02:35
References:
Shared-Refs:
Authors: SOKUMAR.US
Attachments: NONE
Content-Type: TEXT/PLAIN
Keywords: INSTEAD-OF; SQL; TRIGGER; UTILITY; XML; ATTRIBUTE;
Products: 5/RDBMS;
Platforms: GENERIC;

*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************

Overview
--------
This program uses Java programmatic interface of XML SQL Utility (XSU) to insert the
contents of a XML document into multiple database tables.
This program also demonstrates how to overcome the XSU limitation of not being able to
insert XML attribute values into a database table. This program uses a XML Stylesheet(XSL)
to convert the XML attributes into XML elements.


Program Notes
-------------

1). Download appropriate jar files for the XML SQL Utility from technet Web site
depending upon the JDK version and JDBC being used. Extract the downloaded ZIP archive.

2). Set the CLASSPATH environment variable to point to the Java parser, the XML SQL Utility
and the JDBC classes.
For example if you are using JDK 1.2 or above and JDBC classes12_01.zip, you need to use
XSU12.jar and set the CLASSPATH as shown below:
setenv CLASSPATH .:$ORACLE_HOME/jdbc/lib/classes12_01.zip
:$XSU_DIR/OracleXSU12/lib/xmlparserv2.jar:$XSU_DIR/OracleXSU12/lib/xsu12.jar
Here:
Replace $ORACLE_HOME with the path where Oracle is installed.
Replace $XSU_DIR with the path where XML SQL Utility is installed.

3). Create the necessary database objects in scott/tiger schema to map to the structure of the XML document
Exmp1.xml given in the example below. Here two database tables 'custorder' and 'orderlist'
are created for storing the XML document. Table 'custorder' stores the customer
information, and table 'orderlist' stores the order item information. These two
tables are linked with the 'orderid' column that maps to the attribute 'orderid'
in the XML document. A database view 'v_order' is created to join these two tables
such that columns in the view map to the individual top level elements of the XML
document. A database 'instead-of' trigger is created on the view v_order to allow
inserts into its base tables.

create or replace type custobj as object
(
name varchar2(40),
address varchar2(70),
phone varchar2(20)
);

create table custorder
(
orderid number,
customer custobj
);

create or replace type item as object
(
productid number,
productname varchar2(50),
quantity number,
price number
);

create type items as table of item;

create table orderlist
(
orderid number,
itemlist items
)
nested table itemlist store as nested_itemlist;


create or replace view v_order as
select c.orderid, c.customer, o.itemlist
from custorder c, orderlist o
where c.orderid = o.orderid;


create or replace trigger tv_order
instead of insert on v_order
referencing new as n
for each row
begin
insert into custorder(orderid, customer) values (:n.orderid, :n.customer);
insert into orderlist(orderid, itemlist) values (:n.orderid, :n.itemlist);
end;


References
----------
Note: 120841.1

Caution
-------

The sample program in this article is provided for educational purposes only
and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before relying
on it.


Program
-------

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - -
// File name: Examp1.java

// This program parses the input XML and XSL documents. Then It invokes XSLT processor to
// transform the input XML document according to the XSL. It stores the transformed XML
// document into the view 'v_order' using XSU class OracleXMLSave.


// Import the necessary classes
import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.util.*;
import java.io.*;
import java.net.*;
import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;


public class Examp1
{
public static void main(String argv[])
throws SQLException
{
DOMParser parser;
XMLDocument xml, xsldoc;
XMLDocument out = new XMLDocument();
URL xslURL;
URL xmlURL;

try
{

if (argv.length != 2)
{
// Must pass in the names of the XSL and XML files
System.err.println("Usage: java examp1 xslfile xmlfile");
System.exit(1);
}


// Parse xsl and xml documents

parser = new DOMParser(); // Create an instance of XML Parser

// parse the input XSL file
xslURL = createURL(argv[0]);
parser.parse(xslURL);
xsldoc = parser.getDocument();

// parser the input XML file
xmlURL = createURL(argv[1]);
parser.parse(xmlURL);
xml = parser.getDocument();

// Create instances of stylesheet and XSL Processor
XSLStylesheet xsl = new XSLStylesheet(xsldoc, xslURL);
XSLProcessor processor = new XSLProcessor();

// display any warnings that result
processor.showWarnings(true);
processor.setErrorStream(System.err);

// Apply XSL on the XML document
DocumentFragment result = processor.processXSL(xsl, xml);

// create an output document to hold the result
out.appendChild(result);
out.print(System.out);
}
catch (Exception e)
{
e.printStackTrace();
}
try
{
// Establish connection to database
Connection conn = getConnection("scott","tiger");

// Create an instance of OracleXMLSave class
OracleXMLSave sav = new OracleXMLSave(conn, "scott.v_order");

sav.setIgnoreCase(true); // Instruct XSU to ignore case while mapping XML tags
// to table column names

sav.setRowTag("CustOrder"); // Instruct XSU to use each Custorder element as a
// table row.

int rowcount = sav.insertXML(out); // Insert the XML document into the view

// Display number of rows inserted
System.out.println("Number of rows Processed: " + rowcount);

sav.close(); // Close OracleXMLSave -- There is nothing more to insert
conn.close(); // Close the database connection
}
catch (SQLException sqlex)
{
sqlex.printStackTrace();
}
catch (oracle.xml.sql.OracleXMLSQLException osqlex)
{
System.out.println(" Caught SQL Exception:"+ osqlex.getErrorCode());
System.out.println(" Caught SQL Exception:"+ osqlex.getXMLErrorString());
System.out.println(" Caught SQL Exception:"+ osqlex.getXMLSQLErrorString());
}
}


// Get the connection given the user name and password
private static Connection getConnection(String user, String passwd)
throws SQLException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection("jdbcracle:thin:@lang-xdk:1521:L816",user,passwd);
return conn;
}


static URL createURL(String fileName)
{
URL url = null;
try
{
url = new URL(fileName);
}
catch (MalformedURLException ex)
{
File f = new File(fileName);
try
{
String path = f.getAbsolutePath();
// The following code is required to make a
// valid URL on the Windows platform, due to
// inconsistencies in what getAbsolutePath returns.
String fs = System.getProperty("file.separator");
if (fs.length() == 1)
{
char sep = fs.charAt(0);
if (sep != '/')
path = path.replace(sep, '/');
if (path.charAt(0) != '/')
path = '/' + path;
}
path = "file://" + path;
url = new URL(path);
}
catch (MalformedURLException e)
{
System.out.println("Cannot create url for: " + fileName);
System.exit(0);
}
}
return url;
}

}

- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -


Sample Output
-------------

/* [sokumar@lang-xdk sample]$ java examp1 examp1.xsl examp1.xml



Shirley Cohen

2425 skylane, Dallas, TX

615-414-4112



111
Computer
2
2000


113
Monitor
2
865





Vickie Bratt

123, tech blvd. colorado springs co

413-567-8009



111
Computer
1
2000


112
Keyboard
1
300




*/