xml data insert into relational table?

I have table say..

create table customers(
custID number primary key,
company varchar2(100),
city varchar2(100));

and i have xml document as

<?xml version="1.0"?>
         <Company>Bell South</Company>  
         <City>New York</City>
         <Company>Barnes &amp; Noble</Company>  
         <City>New York</City>
         <Company>Comp USA</Company>  

I need to insert the values of custId,company,city into customers relational table column values custid,company,city respectively.

How can i do it. can anybody help me in inserting xml data into relational table?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

schwertnerConnect With a Mentor Commented:
My comment is long, but I hope the info will be helpful for you.

With Oracle 9i some fundamental advances have been made to provide native XML
support in the database, these new features now enable Oracle 9i to understand
and speak XML natively.

Oracle9i supports the generation of XML, on the client or server, where existing
object-relational data can be used to generate the corresponding XML. The
generation of XML can work from query results or be generated as part of the SQL
query itself. XSU (XML-SQL utility) provides a Java and PL/SQL interface to
generating XML from SQL queries. Oracle9i now extends this functionality into
the server, by providing new SQL functions for XML generation and aggregation
and also a C version of the XML-SQL utility linked into the server.

The following native XML support has been added with 9i

1. Introduction of a new XMLType datatype    
  The new XMLType datatype:
    -  Is a native server datatype
    -  With the current release 9.0, XMLType stores XML documents as CLOBs.
    -  Can be used to create columns of this type
    -  Can be used in PLSQL functions and procedures
    -  The XMLType also supports member functions that can be used to extract
       fragments from the XML document.
    -  Oracle9i Text (interMedia Text) index can be used to index such columns
       and query them using the Contains operator and an XPath-like syntax.

2. Native XML generation and storage
    -  DBMS_XMLGen package in PL/SQL can be used to generate XML from SQL
       queries. This package is written in C and linked in to the server for
       better performance. This has a similar interface to DBMS_XMLQuery, in
       order to facilitate migration, if needed.
    -  SYS_XMLGEN() and SYS_XMLAGG() SQL functions for getting XML from SQL
       queries. SYS_XMLGEN operates on rows. SYS_XMLAGG is an aggregation
       function that can combine several XML documents into an aggregated
    -  New table functions can be used to decompose XML document across tables
3. New Native URI-Ref Datatype
    -  DBURIType   : Helps access local data in a database
    -  HTTPURIType : Helps access remote data via HTTP
    -  URIType     : An abstract object type that can store instances of
                     HttpUriType or DBUriType.
    -  Used in XPath syntax for XML navigation

Oracle 9i XML Users Guide
Oracle 9i Application Developers Guide - XML
Doc ID:  Note:120841.1
Subject:  XML SQL Utility
Content Type:  TEXT/PLAIN
Creation Date:  28-SEP-2000
Last Revision Date:  16-FEB-2001


This note briefly describes Oracle's XML-SQL Utility, its capabilities, and how to use it.


This note will introduce XML-SQL Utility to Oracle customers and support personnel.

XML-SQL Utility

Oracle provides an XML SQL Utility (XSU) that allows a structured XML document to be stored
in an Object Relational Database, and also to retrieve the XML document from the database.

XML SQL Utility is a part of the standard installation for Oracle 8i (8.1.7). In the earlier
versions of 8i, the setup for XSU is based on how and where XSU needs to be used. XSU can be
run on the client, in the Web Server, or in the database itself.

XSU is written in Java, and XSU classes are stored in one of the following files depending
upon the environment:

   - XSU12.jar for JDK 1.2 and 8.1.6 database
   - XSU111.jar for jdk 1.1.x and 8.1.5 database

XSU uses Oracle's XML Parser to process the XML documents. The Java classes for XML parser
are stored in XMLPARSERV2.jar file.

XSU also needs the appropriate JDBC driver in order to access the database.

To run XSU on a client or inside the Web Server CLASSPATH environment variable should point
to the required files - XSU jar file, XML Parser jar file, and JDBC Driver zip file.

To run XSU inside the database, Java classes for XSU and XML Parser have to be loaded into
the database.

On a client or in the Web Server, XSU functionality can be accessed through Java Programmatic
Interface. Whereas in the database XSU can be accessed through Java as well as PL/SQL
Programmatic Interface.

To run XSU inside the database, the database server should be Java enabled. On a Web Server,
XSU is invoked from a Java Servlet. Oracle has provided a standard Servlet (XSQL Servlet)
which calls XSU to retrieve and store XML data.

Main Features of XSU are:

 1). Supports Generation of XML documents from any SQL Query.

 2). Supports inserts, updates, and deletes of database records corresponding to a given XML

 3). Supports all Oracle 8i data types.

 4). Supports dynamic generation of Document Type Definition (DTD) while retrieving an XML
     document from the database.

 5). Supports simple transformation of the retrieved XML document using XML Stylesheet (XSL).

Mapping XML Elements to database:

 The mapping of the XML elements to the columns in an Object Relational table is the most
 important consideration while retrieving or storing an XML document. Table columns are
 mapped to top level elements and scalar values are mapped to elements with text-only
 content. Database object types are mapped to XML elements with object attributes appearing
 as sub-elements. Database collections are mapped to lists of elements in the XML document.

 The following example illustrates the mapping process:

   XML document:
     <?xml version="1.0"?>
                <Name>Shirley Cohen</Name>
              <Address>2425 skylane, Dallas, TX</Address>
                  <Item num="1">

              <Item num="2">

   Corresponding Database Definitions:
     create or replace type CustObj as object
       Name varchar2(40),
       Address varchar2(70),
       Phone varchar2(20)
     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 CustOrder
        OrderID number,
        Customer CustObj,
         ItemList Items
       nested table ItemList store as nested_itemlist;

   The above example shows a structured XML document that has three main elements <OrderID>,
   <Customer>, and <ItemList>. These are mapped to three columns in table 'CustOrder'.
   Elements <Customer> and <Item> contain sub-elements in the XML, and therefore database
   objects are created corresponding to them. Element <ItemList> of the XML is mapped to a
   collection of object 'Item'.  

 If an XML document is structured, but the structure of the XML document is not compatible
 with the structure of the underlying database schema, the XML document must be transformed
 before it can be stored into the database. XML documents can be transformed using either XML
 Stylesheet (XSL) or Document Object Model (DOM) Programmatic Interface.
 Another approach to store non-compatible XML is to create a database object view that
 corresponds to the XML document structure. An 'instead-of trigger' must then be created on
 the object view to allow updates to the base table.

 If an XML document is unstructured or has an element that contains large amount of data that
 does not need to be mapped to individual columns of a relational table, it can be stored as
 a Large Object Type (LOB).

 1. Database structures cannot be created based on Document Type Definition (DTD).
 2. Attribute values of an XML element cannot be mapped to a table column. The work-around
    for this limitation is to use a XML Stylesheet (XSL) to convert the attribute to an XML
 3. An XML document can be stored into one database table/view. To store the document into
    multiple tables, a view joining all the tables can be used.

Workarounds for limitations 2 and 3 are shown in Note:121348.1

Invoking XSU:

 XSU can be executed either through a command line interface or through Programmatic
 interfaces written in Java or PL/SQL. This note briefly explains how XSU can be invoked
 through Java and PL/SQL interfaces.

 For Java Programmatic Interface XSU provides two java classes:

   1). OracleXMLQuery: To generate a XML document from a sql query.

     There are three basic steps in generating XML:

       a). Create connection to the database.
       b). Create an Instance of OracleXMLQuery passing either a SQL string or a resultset
       c). Get the result using method getXMLDOM() in the form of a DOM tree or using
             method getXMLString() in the form of string.

       Import oracle.jdbc.driver.*;
       import oracle.xml.sql.query.OracleXMLQuery;
       import java.lang.*;
       import java.sql.*;

       // class to test the String generation!
       class testXMLSQL {

          public static void main(String[] argv)

               // create the connection
               Connection conn  = getConnection("scott","tiger");

             // Create the query class.
             OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp");

             // Get the XML string
             String str = qry.getXMLString();

             // Print the XML output
             System.out.println(" The XML output is:\n"+str);
             // Always close the query to get rid of any resources..
            }catch(SQLException e){

          // Get the connection given the user name and password..!
          private static Connection getConnection(String username, String password)
            throws SQLException
             // register the JDBC driver..
             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

             // Create the connection using the OCI8 driver
             Connection conn =

             return conn;

       OracleXMLQuery provides various other methods to control the query processing and XML
       generation. Some of the useful methods are:

       keepObjectOpen(boolean)    - The default behavior for all the getXML functions
                                      which DO NOT TAKE in a ResultSet object is to close
                                      the ResultSet object and Statement objects at the
                                      end of the call; this method allows for change of
                                      this behaviour. This is very useful when retreiving
                                      few records at a time because it preserves all the
       setDateFormat(String)      - Sets the format of the generated dates in the XML
       setEncoding(String)        - Sets the encoding PI (processing instruction) in the
                                      XML doc.
       setMaxRows(int)            - This sets the maximum number of rows to be retreived
                           from the query result after "skipRows" number of rows
                                      are skipped. You can use one of MAXROWS_NONE,
                                      MAXROWS_ALL, MAXROWS_DEFAULT
       setRaiseException(boolean) - setRaiseException function when called with the value true, will raise
exceptions such as
                                      no-data found, instead of returning a NULL row out.
       setRowsetTag(String)       - Sets the tag to be used to enclose the xml dataset.
       setRowTag(String)          - Sets the tag to be used to enclose the xml element corresponding to
a db.
       setSkipRows(int)           - Sets the number of rows to skip.
       setXSLT(Reader, String)    - Sets the stylesheet to be applied during XML generation.
       setXSLT(String, String)    - Sets the stylesheet to be applied during XML generation.
       useLowerCaseTagNames()     - This will set the case to be lower for all tag names.
       useUpperCaseTagNames()     - This will set the case to be upper for all tag names.


   2). OracleXMLSave: To save a XML document into the database.

       This class provides methods to insert, update and delete table records
       corresponding to a XML document. XSU does not issue any explicit commit after
       insert, update and delete operations. The basic steps in saving a XML document are:

       a). Create connection to database.
       b). Create an instance of OracleXMLSave passing tablename or viewname.
       c). Use insertXML(), updateXML() or deleteXML() methods to perform database
             operations for the specified XML document.

       import java.sql.*;
       import oracle.xml.sql.dml.OracleXMLSave;
       public class testInsert
          public static void main(String argv[])
            throws SQLException
           Connection conn = getConnection("scott","tiger");
           OracleXMLSave sav = new OracleXMLSave(conn, "scott.emp");
           // Assume that the user passes in this document. Save it in to the table.!

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

     OracleXMLSave provides various other methods to control the storage of an XML
     document into the database. Some of the useful methods are:

       setBatchSize(int)             - This call changes the batch size used during DML
       setCommitBatch(int)           - Sets the commit batch size.
       setDateFormat(String)         - Sets the format of the date tags.
       setIgnoreCase(boolean)        - Used to tell XSU to ignores the case of the tag names
                                       when matching them with the column names of the
       setKeyColumnList(String[])    - Sets the list of columns to be used for identifying a
                                       particular row in the database table during update or
       setRowTag(String)             - Names the tag used in the XML doc., to enclose the XML
                                       elements corresponding to each row value.
       setUpdateColumnList(String[]) - Set the column values to be updated.

 For PL/SQL Programmatic Interface XSU provides two packages DBMS_XMLQuery and
 DBMS_XMLSave corresponding to java classes OracleXMLQuery and OraclewXMLSave. These
 packages are available in Oracle 8.1.7. Prior Oracle XSU releases provide package XMLGEN
 that defines methods getXML, insertXML, updateXML, and DeleteXML to perform the retrieval
 and storage of XML documents.

SQL> create table emp1 (empno1 number(4), ename1 varchar2(10));

Table created.

SQL> desc emp1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO1                                             NUMBER(4)
ENAME1                                             VARCHAR2(10)

SQL> insert into emp1 values(1111, 'TEST1');

1 row created.

SQL> insert into emp1 values(2222, 'TEST2');

1 row created.

SQL> select * from emp1;

---------- ----------
     1111 TEST1
     2222 TEST2

After running the program without the commit, the result would be as follows:

You can used the XPATH to navigate through the data of the XML message.
shashikanth0452Author Commented:
I have implemented in java application, i wanna need to implement through pl/sql.

the simplest way to insert xml-date into oracle-database is :

- use canonical xml-format
- use XSU (commandline, programmatically).

Your table is designed: custid,company, city So you can insert this xml-file direct: (customer.xml)

  <company>Your Company</company>

Insert with the following commandline (include xsu12.jar,xmlparserv2.jar to your CLASSPATH):

java OracleXML putXML -user scott/tiger -ignoreCase -filename customer.xml

So if you don't have canonical format, you must translate to it with xsl transformation. (customer.xsl)

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
      <xsl:for-each select="Customers/Customer">
            <custid><xsl:value-of select="CustID"/></custid>
            <company><xsl:value-of select="Company"/></company>
            <city><xsl:value-of select="City"/></city>

You can translate programmtically or from comandline:

java oracle.xml.parser.v2.oraxsl yourdoc.xml customer.xsl customer.xml

And load your converted "customer.xml" to the database.


Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.

To view your open questions, please click the following link(s) and keep them all current with updates.

To view your locked questions, please click the following link(s) and evaluate the proposed answer.

------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
Thank you everyone.
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.