Solved

xml data insert into relational table?

Posted on 2002-03-05
5
3,373 Views
Last Modified: 2013-11-19
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"?>
<Customers>
    <Customer>
         <CustID>1</CustID>
         <Company>Bell South</Company>  
         <City>New York</City>
    </Customer>
    <Customer>
         <CustID>2</CustID>
         <Company>Barnes &amp; Noble</Company>  
         <City>New York</City>
    </Customer>
    <Customer>
         <CustID>3</CustID>
         <Company>Comp USA</Company>  
         <City>Tampa</City>
    </Customer>  
    <Customer>
         <CustID>4</CustID>
         <Company>Borders</Company>      
         <City>Charlotte</City>
    </Customer>
</Customers>


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?
0
Comment
Question by:shashikanth0452
5 Comments
 

Expert Comment

by:marcovandewater
ID: 6843657


You can used the XPATH to navigate through the data of the XML message.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 50 total points
ID: 6843827
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
       document.
    -  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

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


PURPOSE
-------

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


SCOPE & APPLICATION
-------------------

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
     document.

 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"?>
     <ROWSET>
      <CustOrder>
          <OrderID>1</OrderID>
           <Customer>
                <Name>Shirley Cohen</Name>
              <Address>2425 skylane, Dallas, TX</Address>
              <Phone>615-414-4112</Phone>
          </Customer>
   
        <ItemList>
                  <Item num="1">
                <ProductID>111</ProductID>
                   <ProductName>Computer</ProductName>
                   <Quantity>2</Quantity>
                   <Price>2000</Price>
              </Item>

              <Item num="2">
                  <ProductID>113</ProductID>
                 <ProductName>Monitor</ProductName>
                <Price>865</Price>
                  </Item>
          </ItemList>
      </CustOrder>
      </ROWSET>

   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).


Limitations:
 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
    element
 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
             object.
       c). Get the result using method getXMLDOM() in the form of a DOM tree or using
             method getXMLString() in the form of string.

       Example:
       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)
          {

          try{
               // 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..
            qry.close();
            }catch(SQLException e){
               System.out.println(e.toString());
            }
          }

          // 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 =
               DriverManager.getConnection("jdbc:oracle:oci8:@",username,password);

             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
                                      metadata.
       setDateFormat(String)      - Sets the format of the generated dates in the XML
                                      doc.
       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.

       Example:
       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.!
           sav.insertXML(argv[1]);
           sav.close();

         }
         // 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("jdbc:oracle:oci8:@",user,passwd);
             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
                                       operations.
       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
                                       table/view.
       setKeyColumnList(String[])    - Sets the list of columns to be used for identifying a
                                       particular row in the database table during update or
                                       delete.
       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.
 
Example
-------

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;

   EMPNO1 ENAME1
---------- ----------
     1111 TEST1
     2222 TEST2

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

Author Comment

by:shashikanth0452
ID: 6844087
I have implemented in java application, i wanna need to implement through pl/sql.

0
 
LVL 3

Expert Comment

by:graf27
ID: 6849892
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)


<rowset>
<row>
  <custid>1</custid>
  <company>Your Company</company>
  <city>Paris</city>
</row>
</rowset>


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="/">
   <rowset>
      <xsl:for-each select="Customers/Customer">
        <row>
            <custid><xsl:value-of select="CustID"/></custid>
            <company><xsl:value-of select="Company"/></company>
            <city><xsl:value-of select="City"/></city>
       </row>
      </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>
   

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.


0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6863173
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY REGARDING YOUR ACCOUNT AT THIS SITE AND ALL YOUR OPEN QUESTIONS.

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.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
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.
http://www.experts-exchange.com/questions/Q.20144001.html
http://www.experts-exchange.com/questions/Q.20163217.html
http://www.experts-exchange.com/questions/Q.20169492.html
http://www.experts-exchange.com/questions/Q.20170443.html
http://www.experts-exchange.com/questions/Q.20186760.html
http://www.experts-exchange.com/questions/Q.20219348.html
http://www.experts-exchange.com/questions/Q.20275188.html
http://www.experts-exchange.com/questions/Q.20260711.html
http://www.experts-exchange.com/questions/Q.20265968.html
http://www.experts-exchange.com/questions/Q.20269003.html
http://www.experts-exchange.com/questions/Q.20270162.html
http://www.experts-exchange.com/questions/Q.20270635.html
http://www.experts-exchange.com/questions/Q.20144003.html
http://www.experts-exchange.com/questions/Q.20144002.html
http://www.experts-exchange.com/questions/Q.20166334.html
http://www.experts-exchange.com/questions/Q.20176430.html
http://www.experts-exchange.com/questions/Q.20177385.html
http://www.experts-exchange.com/questions/Q.20188259.html
http://www.experts-exchange.com/questions/Q.20191254.html
http://www.experts-exchange.com/questions/Q.20232871.html
http://www.experts-exchange.com/questions/Q.20266616.html
http://www.experts-exchange.com/questions/Q.20270156.html
http://www.experts-exchange.com/questions/Q.20273803.html
http://www.experts-exchange.com/questions/Q.20276230.html


To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20161505.html
http://www.experts-exchange.com/questions/Q.20177382.html
http://www.experts-exchange.com/questions/Q.20178264.html
http://www.experts-exchange.com/questions/Q.20182838.html
http://www.experts-exchange.com/questions/Q.20192648.html
http://www.experts-exchange.com/questions/Q.20195600.html
http://www.experts-exchange.com/questions/Q.20206146.html
http://www.experts-exchange.com/questions/Q.20208927.html
http://www.experts-exchange.com/questions/Q.20223311.html
http://www.experts-exchange.com/questions/Q.20226631.html

PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  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.
 
Moondancer
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now