- 浏览: 23210 次
- 性别:
- 来自: 上海
文章分类
最新评论
初次接触oracle的xmltype
- 博客分类:
- 数据库 xmltype
最近一个项目中用到了oracle的xmltype数据类型,发现这个网站比较详细,记录一下,比较简单,例子就不上传了:
http://stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10790/xdb11jav.htm#i1030427
12 Java API for XMLType
This chapter describes how to use XMLType
in Java, including fetching XMLType
data through JDBC.
This chapter contains these topics:
Introducing Java DOM API for XMLType
Oracle XML DB supports the Java Document Object Model (DOM) Application Program Interface (API) for XMLType. This is a generic API for client and server, for both XML schema-based and non- schema-based documents. It is implemented using the Java package oracle.xdb.dom
. DOM is an in-memory tree-based object representation of XML documents that enables programmatic access to their elements and attributes. The DOM object and interface are part of a W3C recommendation. DOM views the parsed document as a tree of objects.
To access XMLType
data using JDBC use the class oracle.xdb.XMLType
.
For XML documents that do not conform to any XML schema, you can use the Java DOM API for XMLType
because it can handle any valid XML document.
Java DOM API for XMLType
Java DOM API for XMLType
handles all kinds of valid XML documents irrespective of how they are stored in Oracle XML DB. It presents to the application a uniform view of the XML document irrespective of whether it is XML schema-based or non- schema-based, whatever the underlying storage. Java DOM API works on client and server.
As discussed in Chapter 10, " PL/SQL API for XMLType " , the Oracle XML DB DOM APIs are compliant with W3C DOM Level 1.0 and Level 2.0 Core Recommendation.
Java DOM API for XMLType
can be used to construct an XMLType
instance from data encoded in different character sets. It also provides a new getBlobVal()
method to retrieve the XML contents in the requested character set.
Accessing XML Documents in Repository
Oracle XML DB resource API for Java API allows Java applications to access XML documents stored in the Oracle XML DB repository. Naming conforms to the Java binding for DOM as specified by the W3C DOM Recommendation. Oracle XML DB repository hierarchy can store both XML schema-based and non- schema-based documents.
Accessing XML Documents Stored in Oracle Database (Java)
Oracle XML DB provides the following way (part of the Java Resource APIs) for Java applications to access XML data stored in a database:
Using JDBC to Access XMLType Data
This is a SQL-based approach for Java applications for accessing any data in Oracle Database, including XML documents in Oracle XML DB. Use the oracle.xdb.XMLType
class, createXML()
method.
How Java Applications Use JDBC to Access XML Documents in Oracle XML DB
JDBC users can query an XMLType
table to obtain a JDBC XMLType
interface that supports all methods supported by the SQL XMLType
data type. The Java (JDBC) API for XMLType
interface can implement the DOM document interface.
Example 12-1 XMLType Java: Using JDBC to Query an XMLType Table
The following is an example that illustrates using JDBC to query an XMLType
table:
import oracle.xdb.XMLType; ... OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("select e.poDoc from po_xml_tab e"); ResultSet rset = stmt.executeQuery(); OracleResultSet orset = (OracleResultSet) rset; while(orset.next()) { // get the XMLType XMLType poxml = XMLType.createXML(orset.getOPAQUE(1)); // get the XMLDocument as a string... Document podoc = (Document)poxml.getDOM(); }
Example 12-2 XMLType Java: Selecting XMLType Data
You can select the XMLType
data in JDBC in one of two ways:
-
Use the
getClobVal()
,getStringVal()
orgetBlobVal(csid)
in SQL and get the result as anoracle.sql.CLOB, java.lang.String
ororacle.sql.BLOB
in Java. The following Java code snippet shows how to do this:DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@", "scott", "tiger"); OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement( "select e.poDoc.getClobVal() poDoc, "+ "e.poDoc.getStringVal() poString "+ " from po_xml_tab e"); ResultSet rset = stmt.executeQuery(); OracleResultSet orset = (OracleResultSet) rset; while(orset.next()) { // the first argument is a CLOB oracle.sql.CLOB clb = orset.getCLOB(1); // the second argument is a string.. String poString = orset.getString(2); // now use the CLOB inside the program }
-
Use
getOPAQUE()
call in thePreparedStatement
to get the wholeXMLType
instance, and use theXMLType
constructor to construct anoracle.xdb.XMLType
class out of it. Then you can use the Java functions on theXMLType
class to access the data.import oracle.xdb.XMLType; ... OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement( "select e.poDoc from po_xml_tab e"); ResultSet rset = stmt.executeQuery(); OracleResultSet orset = (OracleResultSet) rset; // get the XMLType XMLType poxml = XMLType(orset.getOPAQUE(1)); // get the XML as a string... String poString = poxml.getStringVal();
Example 12-3 XMLType Java: Directly Returning XMLType Data
This example shows the use of getObject
to directly get the XMLType
from the ResultSet
. This code snippet is the easiest way to get the XMLType
from the ResultSet
.
import oracle.xdb.XMLType; ... PreparedStatement stmt = conn.prepareStatement( "select e.poDoc from po_xml_tab e"); ResultSet rset = stmt.executeQuery(); while(rset.next()) { // get the XMLType XMLType poxml = (XMLType)rset.getObject(1); // get the XML as a string... String poString = poxml.getStringVal(); }
Example 12-4 XMLType Java: Returning XMLType Data and Registering the Output Parameter as XMLType
This example illustrates how to bind an OUT
variable of XMLType
to a SQL statement.
public void doCall (String[] args) throws Exception { // create or replace function getPurchaseOrder(reference varchar2) // return XMLType // as // xml XMLType; // begin // select value(p) // into xml // from PURCHASEORDER p // where extractValue(value(p),'/PurchaseOrder/Reference') = reference; // return xml; // end; String SQLTEXT = "{? = call getPurchaseOrder('BLAKE-2002100912333601PDT')}"; CallableStatement sqlStatement = null; XMLType xml = null; super.doSomething(args); createConnection(); try { System.out.println("SQL := " + SQLTEXT); sqlStatement = getConnection().prepareCall(SQLTEXT); sqlStatement.registerOutParameter (1, OracleTypes.OPAQUE,"SYS.XMLTYPE"); sqlStatement.execute(); xml = (XMLType) sqlStatement.getObject(1); System.out.println(xml.getStringVal()); } catch (SQLException SQLe) { if (sqlStatement != null) { sqlStatement.close(); throw SQLe; } }
Using JDBC to Manipulate XML Documents Stored in a Database
You can also update, insert, and delete XMLType
data using JDBC.
Note: extract() , transform() , and existsNode() methods only work with the Thick JDBC driver.
Not all |
Example 12-5 XMLType Java: Updating, Inserting, or Deleting XMLType Data
You can insert an XMLType
in Java in one of two ways:
-
Bind a CLOB or a string to an
INSERT
or UPDATE orDELETE
statement, and use theXMLType
constructor inside SQL to construct the XML instance:OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement( "update po_xml_tab set poDoc = XMLType(?) "); // the second argument is a string.. String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>"; // now bind the string.. stmt.setString(1,poString); stmt.execute();
-
Use the
setObject()
orsetOPAQUE()
call in thePreparedStatement
to set the wholeXMLType
instance:import oracle.xdb.XMLType; ... OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement( "update po_xml_tab set poDoc = ? "); // the second argument is a string String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>"; XMLType poXML = XMLType.createXML(conn, poString); // now bind the string.. stmt.setObject(1,poXML); stmt.execute();
Example 12-6 XMLType Java: Getting Metadata on XMLType
When selecting out XMLType
values, JDBC describes the column as an OPAQUE
type. You can select the column type name out and compare it with "XMLTYPE
" to check if you are dealing with an XMLType
:
import oracle.sql.*; import oracle.jdbc.*; ... OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement( "select poDoc from po_xml_tab"); OracleResultSet rset = (OracleResultSet)stmt.exuecuteQuery(); // Now, we can get the resultset metadata OracleResultSetMetaData mdata = (OracleResultSetMetaData)rset.getMetaData(); // Describe the column = the column type comes out as OPAQUE // and column type name comes out as XMLTYPE if (mdata.getColumnType(1) == OracleTypes.OPAQUE && mdata.getColumnTypeName(1).compareTo("SYS.XMLTYPE") == 0) { // we know it is an XMLtype }
Example 12-7 XMLType Java: Updating an Element in an XMLType Column
This example updates the discount
element inside PurchaseOrder stored in an XMLType
column. It uses Java (JDBC) and the oracle.xdb.XMLType
class. This example also shows you how to insert, update, or delete XMLType
s using Java (JDBC). It uses the parser to update an in-memory DOM tree and write the updated XML value to the column.
-- create po_xml_hist table to store old PurchaseOrders create table po_xml_hist ( xpo XMLType ); /* DESCRIPTION Example for oracle.xdb.XMLType NOTES Have classes12.zip, xmlparserv2.jar, and xdb.jar in CLASSPATH */ import java.sql.*; import java.io.*; import oracle.xml.parser.v2.*; import org.xml.sax.*; import org.w3c.dom.*; import oracle.jdbc.driver.*; import oracle.sql.*; import oracle.xdb.XMLType; public class tkxmtpje { static String conStr = "jdbc:oracle:oci8:@"; static String user = "scott"; static String pass = "tiger"; static String qryStr = "SELECT x.poDoc from po_xml_tab x "+ "WHERE x.poDoc.extract('/PO/PONO/text()').getNumberVal()=200"; static String updateXML(String xmlTypeStr) { System.out.println("\n==============================="); System.out.println("xmlType.getStringVal():"); System.out.println(xmlTypeStr); System.out.println("==============================="); String outXML = null; try{ DOMParser parser = new DOMParser(); parser.setValidationMode(false); parser.setPreserveWhitespace (true); parser.parse(new StringReader(xmlTypeStr)); System.out.println("xmlType.getStringVal(): xml String is well-formed"); XMLDocument doc = parser.getDocument(); NodeList nl = doc.getElementsByTagName("DISCOUNT"); for(int i=0;i<nl.getLength();i++){ XMLElement discount = (XMLElement)nl.item(i); XMLNode textNode = (XMLNode)discount.getFirstChild(); textNode.setNodeValue("10"); } StringWriter sw = new StringWriter(); doc.print(new PrintWriter(sw)); outXML = sw.toString(); //print modified xml System.out.println("\n==============================="); System.out.println("Updated PurchaseOrder:"); System.out.println(outXML); System.out.println("==============================="); } catch ( Exception e ) { e.printStackTrace(System.out); } return outXML; } public static void main(String args[]) throws Exception { try{ System.out.println("qryStr="+ qryStr); DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@", user, pass); Statement s = conn.createStatement(); OraclePreparedStatement stmt; ResultSet rset = s.executeQuery(qryStr); OracleResultSet orset = (OracleResultSet) rset; while(orset.next()){ //retrieve PurchaseOrder xml document from database XMLType xt = XMLType.createXML(orset.getOPAQUE(1)); //store this PurchaseOrder in po_xml_hist table stmt = (OraclePreparedStatement)conn.prepareStatement( "insert into po_xml_hist values(?)"); stmt.setObject(1,xt); // bind the XMLType instance stmt.execute(); //update "DISCOUNT" element String newXML = updateXML(xt.getStringVal()); // create a new instance of an XMLtype from the updated value xt = XMLType.createXML(conn,newXML); // update PurchaseOrder xml document in database stmt = (OraclePreparedStatement)conn.prepareStatement( "update po_xml_tab x set x.poDoc =? where "+ "x.poDoc.extract('/PO/PONO/text()').getNumberVal()=200"); stmt.setObject(1,xt); // bind the XMLType instance stmt.execute(); conn.commit(); System.out.println("PurchaseOrder 200 Updated!"); } //delete PurchaseOrder 1001 s.execute("delete from po_xml x"+ "where x.xpo.extract"+ "('/PurchaseOrder/PONO/text()').getNumberVal()=1001"); System.out.println("PurchaseOrder 1001 deleted!"); } catch( Exception e ) { e.printStackTrace(System.out); } } } ---------------------- -- list PurchaseOrders ---------------------- set long 20000 set pages 100 select x.xpo.getClobVal() from po_xml x;
Here is the resulting updated purchase order in XML:
<?xml version = "1.0"?> <PurchaseOrder> <PONO>200</PONO> <CUSTOMER> <CUSTNO>2</CUSTNO> <CUSTNAME>John Nike</CUSTNAME> <ADDRESS> <STREET>323 College Drive</STREET> <CITY>Edison</CITY> <STATE>NJ</STATE> <ZIP>08820</ZIP> </ADDRESS> <PHONELIST> <VARCHAR2>609-555-1212</VARCHAR2> <VARCHAR2>201-555-1212</VARCHAR2> </PHONELIST> </CUSTOMER> <ORDERDATE>20-APR-97</ORDERDATE> <SHIPDATE>20-MAY-97 12.00.00.000000 AM</SHIPDATE> <LINEITEMS> <LINEITEM_TYP LineItemNo="1"> <ITEM StockNo="1004"> <PRICE>6750</PRICE> <TAXRATE>2</TAXRATE> </ITEM> <QUANTITY>1</QUANTITY> <DISCOUNT>10</DISCOUNT> </LINEITEM_TYP> <LINEITEM_TYP LineItemNo="2"> <ITEM StockNo="1011"> <PRICE>4500.23</PRICE> <TAXRATE>2</TAXRATE> </ITEM> <QUANTITY>2</QUANTITY> <DISCOUNT>10</DISCOUNT> </LINEITEM_TYP> </LINEITEMS> <SHIPTOADDR> <STREET>55 Madison Ave</STREET> <CITY>Madison</CITY> <STATE>WI</STATE> <ZIP>53715</ZIP> </SHIPTOADDR> </PurchaseOrder>
-
Selects an
XMLType
from anXMLType
table -
Extracts portions of the
XMLType
based on an XPath expression -
Checks for the existence of elements
-
Transforms the
XMLType
to another XML format based on XSL -
Checks the validity of the
XMLType
document against an XML schema
import java.sql.*; import java.io.*; import java.net.*; import java.util.*; import oracle.xml.parser.v2.*; import oracle.xml.parser.schema.*; import org.xml.sax.*; import org.w3c.dom.*; import oracle.xml.sql.dataset.*; import oracle.xml.sql.query.*; import oracle.xml.sql.docgen.*; import oracle.xml.sql.*; import oracle.jdbc.driver.*; import oracle.sql.*; import oracle.xdb.XMLType; public class tkxmtpk1 { static String conStr = "jdbc:oracle:oci8:@"; static String user = "tpjc"; static String pass = "tpjc"; static String qryStr = "select x.resume from t1 x where id<3"; static String xslStr = "<?xml version='1.0'?> " + "<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1 999/XSL/Transform'> " + "<xsl:template match='ROOT'> " + "<xsl:apply-templates/> " + "</xsl:template> " + "<xsl:template match='NAME'> " + "<html> " + " <body> " + " This is Test " + " </body> " + "</html> " + "</xsl:template> " + "</xsl:stylesheet>"; static void parseArg(String args[]) { conStr = (args.length >= 1 ? args[0]:conStr); user = (args.length >= 2 ? args[1].substring(0, args[1].indexOf("/")):user); pass = (args.length >= 2 ? args[1].substring(args[1].indexOf("/")+1):pass); qryStr = (args.length >= 3 ? args[2]:qryStr); } /** * Print the byte array contents */ static void showValue(byte[] bytes) throws SQLException { if (bytes == null) System.out.println("null"); else if (bytes.length == 0) System.out.println("empty"); else { for(int i=0; i<bytes.length; i++) System.out.print((bytes[i]&0xff)+" "); System.out.println(); } } public static void main(String args[]) throws Exception { tkxmjnd1 util = new tkxmjnd1(); try{ if( args != null ) parseArg(args); // System.out.println("conStr=" + conStr); System.out.println("user/pass=" + user + "/" +pass ); System.out.println("qryStr="+ qryStr); DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection(conStr, user, pass); Statement s = conn.createStatement(); ResultSet rset = s.executeQuery(qryStr); OracleResultSet orset = (OracleResultSet) rset; OPAQUE xml; while(orset.next()){ xml = orset.getOPAQUE(1); oracle.xdb.XMLType xt = oracle.xdb.XMLType.createXML(xml); System.out.println("Testing getDOM() ..."); Document doc = xt.getDOM(); util.printDocument(doc); System.out.println("Testing getBytesValue() ..."); showValue(xt.getBytesValue()); System.out.println("Testing existsNode() ..."); try { System.out.println("existsNode(/)" + xt.existsNode("/", null)); } catch (SQLException e) { System.out.println("Thin driver Expected exception: " + e); } System.out.println("Testing extract() ..."); try { XMLType xt1 = xt.extract("/RESUME", null); System.out.println("extract RESUME: " + xt1.getStringVal()); System.out.println("should be Fragment: " + xt1.isFragment()); } catch (SQLException e) { System.out.println("Thin driver Expected exception: " + e); } System.out.println("Testing isFragment() ..."); try { System.out.println("isFragment = " + xt.isFragment()); } catch (SQLException e) { System.out.println("Thin driver Expected exception: " + e); } System.out.println("Testing isSchemaValid() ..."); try { System.out.println("isSchemaValid(): " + xt.isSchemaValid(null,"RES UME")); } catch (SQLException e) { System.out.println("Thin driver Expected exception: " + e); } System.out.println("Testing transform() ..."); System.out.println("XSLDOC: \n" + xslStr + "\n"); try { /* XMLType xslDoc = XMLType.createXML(conn, xslStr); System.out.println("XSLDOC Generated"); System.out.println("After transformation:\n" + (xt.transform(xslDoc, null)).getStringVal()); */ System.out.println("After transformation:\n" + (xt.transform(null, null)).getStringVal()); } catch (SQLException e) { System.out.println("Thin driver Expected exception: " + e); } System.out.println("Testing createXML(conn, doc) ..."); try { XMLType xt1 = XMLType.createXML(conn, doc); System.out.println(xt1.getStringVal()); } catch (SQLException e) { System.out.println("Got exception: " + e); } } } catch( Exception e ) { e.printStackTrace(System.out); } } }
Loading a Large XML Document into the Database With JDBC
If a large XML document (greater than 4000 characters, typically) is inserted into an XMLType
table or column using a String
object in JDBC, this run-time error occurs:
"java.sql.SQLException: Data size bigger than max size for this type"
This error can be avoided by using a Java CLOB
object to hold the large XML document. Example 12-9 demonstrates this technique, loading a large document into an XMLType
column; the same approach can be used for XMLType
tables. The CLOB
object is created using class oracle.sql.CLOB
on the client side. This class is the Oracle JDBC driver implementation of the standard JDBC interface java.sql.Clob
.
Example 12-9 Loading a Large XML Document
In this example, method insertXML()
inserts a large XML document into the purchaseOrder
XMLType
column of table poTable
. It uses a CLOB
object containing the XML document to do this. The CLOB
object is bound to a JDBC prepared statement, which inserts the data into the XMLType
column.
Prerequisites for running this example are as follows:
-
Oracle Database, version 9.2.0.1 or later.
-
Classes12.zip
orClasses12.jar
, available inORACLE_HOME\jdbc\lib
, should be included in theCLASSPATH
environment variable. -
The target database table. Execute the following SQL before running the example:
CREATE TABLE poTable (purchaseOrder XMLType);
Method insertXML()
The formal parameters of method insertXML()
are as follows:
-
xmlData
– XML data to be inserted into theXMLType
column -
conn
– database connection object (Oracle Connection Object)
... import oracle.sql.CLOB; import java.sql.Connection; import java.sql.SQLException; import java.sql.PreparedStatement; ... private void insertXML (String xmlData, Connection conn) { CLOB clob = null; String query; // Initialize statement Object PreparedStatement pstmt = null; try{ query = "INSERT INTO potable (purchaseOrder) VALUES (XMLType(?)) "; // Get the statement Object pstmt = conn.prepareStatement(query); // xmlData is the string that contains the XML Data. // Get the CLOB object using the getCLOB method. clob = getCLOB(xmlData, conn); // Bind this CLOB with the prepared Statement pstmt.setObject(1, clob); // Execute the Prepared Statement if (pstmt.executeUpdate () == 1) { System.out.println ("Successfully inserted a Purchase Order"); } } catch(SQLException sqlexp){ sqlexp.printStackTrace(); } catch(Exception exp){ exp.printStackTrace(); } }
Method getCLOB()
Method insertXML()
calls method getCLOB()
to create and return the CLOB
object that holds the XML data. The formal parameters of getCLOB()
are as follows:
-
xmlData
– XML data to be inserted into theXMLType
column -
conn
– database connection object (Oracle Connection Object)
... import oracle.sql.CLOB; import java.sql.Connection; import java.sql.SQLException; import java.io.Writer; ... private CLOB getCLOB (String xmlData, Connection conn) throws SQLException{ CLOB tempClob = null; try{ // If the temporary CLOB has not yet been created, create one tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION); // Open the temporary CLOB in readwrite mode, to enable writing tempClob.open(CLOB.MODE_READWRITE); // Get the output stream to write Writer tempClobWriter = tempClob.getCharacterOutputStream(); // Write the data into the temporary CLOB tempClobWriter.write(xmlData); // Flush and close the stream tempClobWriter.flush(); tempClobWriter.close(); // Close the temporary CLOB tempClob.close(); } catch(SQLException sqlexp){ tempClob.freeTemporary(); sqlexp.printStackTrace(); } catch(Exception exp){ tempClob.freeTemporary(); exp.printStackTrace(); } return tempClob; }
Java DOM API for XMLType Features
When using Java DOM API to retrieve XML data from Oracle XML DB, you get the following results:
-
If the connection is thin, then you get an
XMLDocument
instance -
If the Connection is thick or kprb, then you get an
XDBDocument
instance
Both of these are instances of the W3C Document Object Model (DOM) interface. From this document interface you can access the document elements and perform all the operations specified in the W3C DOM Recommendation. The DOM works on:
-
Any type of XML document:
-
XML schema-based
-
Non-XML schema-based
-
-
Any type of underlying storage used by the document:
-
Character Large Object (CLOB)
-
Binary Large Object (BLOB)
-
Object-relational.
-
The Java DOM API for XMLType
supports deep or shallow searching in the document to retrieve children and properties of XML objects such as name, namespace, and so on. Conforming to the DOM 2.0 recommendation, Java DOM API for XMLType
is namespace aware.
Creating XML Documents Programmatically
Java API for XMLType
also allows applications to create XML documents programmatically. This way applications can create XML documents on the fly (or dynamically) that either conform to a preregistered XML schema or are non-XML schema-based documents.
Creating XML Schema-Based Documents
To create XML schema-based documents, Java DOM API for XMLType
uses an extension to specify which XML schema URL to use. For XML schema-based documents, it also verifies that the DOM being created conforms to the specified XML schema, that is, that the appropriate children are being inserted under the appropriate documents.
Note: In this release, Java DOM API forXMLType does not perform type and constraint checks. |
Once the DOM object has been created, it can be saved to Oracle XML DB repository using the Oracle XML DB resource API for Java. The XML document is stored in the appropriate format:
-
As a BLOB for non-XML schema-based documents.
-
In the format specified by the XML schema for XML schema-based documents.
Example 12-10 Java DOM API for XMLType: Creating a DOM Object and Storing It in the Format Specified by the XML Schema
The following example shows how you can use Java DOM API for XMLType
to create a DOM object and store it in the format specified by the XML schema. Note that the validation against the XML schema is not shown here.
import oracle.xdb.XMLType; ... OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement( "update po_xml_XMLTypetab set poDoc = ? "); // the second argument is a string String poString = "<PO><PONO>200</PONO><PNAME>PO_2</PNAME></PO>"; XMLType poXML = XMLType.createXML(conn, poString); Document poDOM = (Document)poXML.getDOM(); Element rootElem = poDOM.createElement("PO"); poDOM.insertBefore(poDOM, rootElem, null); // now bind the string.. stmt.setObject(1,poXML); stmt.execute();
JDBC or SQLJ
An XMLType
instance is represented in Java by oracle.xdb.XMLType
. When an instance of XMLType
is fetched using JDBC, it is automatically manifested as an object of the provided XMLType
class. Similarly, objects of this class can be bound as values to Data Manipulation Language (DML) statements where an XMLType
is expected. The same action is supported in SQLJ clients.
Note: The SQLJ precompiler has been desupported from Oracle Database 10g release 1 (10.1) and Oracle Application Server 10g release 1 (10.1). Oracle9i release 2 (9.2) and Oracle9i AS release 9.0.4 are the last Oracle products to offer SQLJ support. From this release, only the SQLJ precompiler that generates.java files from .sqlj files is desupported through both command-line and JDeveloper. However, both the client-side and server-side SQLJ runtimes are maintained to support JPublisher and existing precompiled SQLJ applications. No new SQLJ application development is possible using Oracle products. Customer priority one (P1) bugs will continue to be fixed. Although the term SQLJ runtime has been renamed to JPublisher runtime, the term SQLJ Object Types is still used. |
Java DOM API for XMLType Classes
Oracle XML DB supports the W3C DOM Level 2 Recommendation. In addition to the W3C Recommendation, Oracle XML DB DOM API also provides Oracle-specific extensions, to facilitate your application interfacing with Oracle XDK for Java. A list of the Oracle extensions is found at:
http://otn.oracle.com/tech/xml/index.html
XDBDocument
is a class that represents the DOM for the instantiated XML document. You can retrieve the XMLType
from the XML document using the function getXMLType()
on XDBDocument
class.
Table 12-1 lists the Java DOM API for XMLType
classes and the W3C DOM interfaces they implement.
Table 12-1 Java DOM API for XMLType: Classes
oracle.xdb.dom.XDBDocument |
org.w3c.dom.Document |
oracle.xdb.dom.XDBCData |
org.w3c.dom.CDataSection |
oracle.xdb.dom.XDBComment |
org.w3c.dom.Comment |
oracle.xdb.dom.XDBProcInst |
org.w3c.dom.ProcessingInstruction |
oracle.xdb.dom.XDBText |
org.w3c.dom.Text |
oracle.xdb.dom.XDBEntity |
org.w3c.dom.Entity |
oracle.xdb.dom.DTD |
org.w3c.dom.DocumentType |
oracle.xdb.dom.XDBNotation |
org.w3c.dom.Notation |
oracle.xdb.dom.XDBNodeList |
org.w3c.dom.NodeList |
oracle.xdb.dom.XDBAttribute |
org.w3c.dom.Attribute |
oracle.xdb.dom.XDBDOMImplementation |
org.w3c.dom.DOMImplementation |
oracle.xdb.dom.XDBElement |
org.w3c.dom.Element |
oracle.xdb.dom.XDBNamedNodeMap |
org.w3c.dom.NamedNodeMap |
oracle.xdb.dom.XDBNode |
org.w3c.dom.Node |
Java Methods Not Supported
The following are methods documented in release 2 (9.2.0.1) but not currently supported:
-
XDBDocument.getElementByID
-
XDBDocument.importNode
-
XDBNode.normalize
-
XDBNode.isSupported
-
XDBDomImplementation.hasFeature
Java DOM API for XMLType: Calling Sequence
The following Java DOM API for XMLType
calling sequence description assumes that your XML data is pre-registered with an XML schema and that it is stored in an XMLType
datatype column. To use the Java DOM API for XMLType
, follow these steps:
-
Retrieve the XML data from the
XMLType
table orXMLType
column in the table. When you fetch XML data, Oracle creates an instance of anXMLType
. You can then use thegetDom()
method to retrieve a Document instance. You can then manipulate elements in the DOM tree using Java DOM API forXMLType
. -
Use the Java DOM API for
XMLType
to perform operations and manipulations on elements of the DOM tree. -
The
XMLType
holds the modified data, but the data is sent back using a JDBC update.
The XMLType
and XDBDocument
objects should be closed using the close()
method in the respective classes. This releases any underlying memory that is held.
发表评论
文章已被作者锁定,不允许评论。
相关推荐
java中xml文件的处理及oracle中xmltype的插入和读取.pdf
oracle操作xmltype
XMLType是Oracle支持的一种基于XML格式存储的数据类型,这里我们共同来探究Java使用JDBC或MyBatis框架向Oracle中插入XMLType数据的方法:
oracle数据库xmltype类型解析使用的jar包,xdb6.jar、xmlparserv2.jar
Oracle P/L SQL实现FTP上传、下载功能,以下是此过程包的头部,包体经常打包处理plb,感兴趣用户可以下载下来。 --Oracle上的FTP功能 Create or Replace Package UTL_FTP AUTHID CURRENT_USER as Type Connection ...
oracle解析XMLTYPE字段里面节点值,并用PL/SQL将查询结果导出为excel
。。。
oracle xmltype 类型 jdbc 的相关jar包 包含 ojdbc8.jar orai18n.jar xdb6.jar xdb.jar xmlparserv2.jar
XMLTYPE_instance:XML数据类型的字段,用于保存XML ,XPath_string:操作的节点路径,例如:/info/Owner。 绝对路径与相对路径:区别在于路径字符串最前面的斜杠是否存在,有斜杠代表绝对路径,没有斜杠则是相对路径...
The new edition covers calls to Java methods from within PL/SQL programs, autonomous transactions, object type inheritance, and the new Timestamp and XMLType data types. There’s also more ...
2.1.1创建一个有XMLType类型字段的表: create table xmlcontent (keyvalue varchar2(10) primary key, xmlvalue xmltype); 2.1.2创建一个XMLType类型表 create table xmltable of xmltype; 2.1.3从XML文件中...
Oracle官方文档 介绍Oracle XML 相关内容,注意此文档...包括 在ORACLE通过相关函数解析XML 如:xmltype、extract 、EXISTSNODE、EXTRACTVALUE等 案例多,这本手册适合需要在Oralce数据库里解析和处理xml 的同学学习。
XMLtype 是一个基于控制台的 UTF-8 编码的面向 XML 文档的文件的编辑器。 它从一开始就设计用于多语言使用,甚至用于编写双向文本(例如混合英语和希伯来语)。
1. EXTRACT(XMLType_instance,Xpath_string) 该函数用于返回XML节点路径下的相应内容 示例: 代码如下:SELECT extract(value(a),’/root/main’) data FROM xmltable a ; 2. EXTRACTVALUE(XMLType_instance,...
从Oracle到PostgreSQL迁移工具 ora_migrator为插件使用迁移Oracle数据库到PostgreSQL。... 这确定LONG,LONG RAW和XMLTYPE列的最大长度。 先决条件 您需要PostgreSQL 9.5或更高版本。 必须安装oracle_fdw和db_migrator
xml 解析方式有四种--DOM 、SAX、JDOM、DOM4J。
Oracle P/L SQL实现发送Email、浏览网页等网络操作功能 Oracle P/L SQL实现发送Email、浏览网页等网络操作功能,以下是此过程包的头部,包体经常打包处理plb,感兴趣用户可以下载下来。 --.使用聚合函数实现 多行...