DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: XSU

Hybrid View

  1. #1
    Join Date
    May 2001

    Question XSU

    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

    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 .

  2. #2
    Join Date
    Apr 2001

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

    Hope this will be helpful

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

    Circulation: REVIEWED (EXTERNAL)
    Folder: languages.XML
    Topic: Sample Code Articles
    Title: How to store XML data across multiple tables using XML SQL
    Document-Type: SCRIPT
    Impact: MEDIUM
    Skill-Level: NOVICE
    Server-Version: 8I
    Updated-Date: 11-JAN-2001 06:02:35
    Authors: SOKUMAR.US
    Attachments: NONE
    Content-Type: TEXT/PLAIN
    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.

    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
    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
    insert into custorder(orderid, customer) values (:n.orderid, :n.customer);
    insert into orderlist(orderid, itemlist) values (:n.orderid, :n.itemlist);

    Note: 120841.1


    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.


    - - - - - - - - - - - - - - - - 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;


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

    // Parse xsl and xml documents

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

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

    // parser the input XML file
    xmlURL = createURL(argv[1]);
    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

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

    // create an output document to hold the result
    catch (Exception e)
    // 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)
    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 =
    return conn;

    static URL createURL(String fileName)
    URL url = null;
    url = new URL(fileName);
    catch (MalformedURLException ex)
    File f = new File(fileName);
    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);
    return url;


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

    Sample Output

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

    Shirley Cohen
    2425 skylane, Dallas, TX




    Vickie Bratt
    123, tech blvd. colorado springs co





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width