Oracle9 XML SQL Utility Technical Guidance (Favorites) (Reposted)
Oracle9 XML SQL Utility Technical Guidance (Java)
■ Pre-development requirements ■ SELECT XML format data ■ INSERT XML format data ■ Updata XML format data ■ delete XML format data
Before the development, you must install the Oracle client to point ClassPath to classpath111.zipxmlparserv2.jarxsu111.jarxsu12.jar
SELECT XML format data has similar hierarchical data relationships between the two, and the relational database mode can simulate. Assuming a database of a book list, the BookList table has the following columns: BookID, Title, Author, Publisher, Year, ISBN, and description. The following is a typical query for the application for the database: SELECT TITLE, AUTHOR, PUBLISHER, YEAR, ISBN from Booklist where bookid = 1234; If you submit a query by orle XML SQL Utility, the database will return the following results: The Difference Between God and Larry Ellison: Inside Oracle Corporation </ Title> <Author> Mike Wilson <Author> <Publtsher> William Morrow and Co . </ Publisher> <iSBN> 1997 </ year> <ISBN> 0688149251 <ISBN> 0688149251 <ISBN> </ ROWSET> If the application only wants to write the output to the file, you can use a string. Such as</p>
<p>If the Oracle XML parser is sent directly to the XSLT processor, you can output it in the form of a DOM object. Providing the DOM output can not be required without parsing, otherwise implement parsing operations before applying XSL conversion. As shown in the following code segment, by passing the query to the oranle.xml.sql.Query.OrCle XML Query class, IMPORT JAVA. SQL. *; Import java. Math. *; Import oracle. XML. SQL .query . *; import oracle. jdbc. driver. *; public static void main (string args []) throws sqlexception {string tabname = "booklist"; string user = "scott / tiger"; DriverManager. registerDriver (new oracle jdbc .driver .oracleDriver ().); Connection conn = DriverManager getConnection. ( "jdbc: oracle: oci8" user "@"); OracleXMLQuery qry = new OracleXMLQuery (conn, "select * from tabName" ); String xmlstring = QRY. GetXmlstring (); system. Out .println ("Output is: / n" xmlstring "); conn. Close (); Oracle XML SQL Utility also provides an optional command line interface, which is used The DTD associated with special database mode is generated. Assume that all content is installed correctly, you only need to perform the following command to get the full command line selection list: Java OracleXML The following command line is used to create a special database that is inquiry Mode-related DTD: Java OracleXML getXml? Scott / Tiger "Scott / Tiger" -withdtd "Select" * from booklist "For the BookList table, the Oracle XML SQL Utility generated in the XML document generated in the query will output the following DTD:</p>
<p><! Element Booklist (Bookid, Title, Author, Publisher, Year, ISBN, DESCRIPTION> <! Element Bookid (#pcdata)> <! Element Author (#pcdata)> <! Element Publisher (#pcdata)> <! Element Year (#pcdata)> <element isbn (#ppcdata)> <! Element Description (#pcdata)></p>
<p>Insert XML Format Data Once a pattern is created in the database, as long as the data in the XML format is consistent with DTD generated from the mode, the XML SQL Utility saves the data to the mode. XML SQL Utility provides the ability to map XML documents to table rows. This storage uses a simple mapping to map the element marker name as a column, convert the XML string into a suitable data type by default mapping. When the XML element has a child element, the XML element is mapped to the SQL object type. In order to save data in XML format, XML SQL Utility launches a plug-in statement, binds all element values in the VALUES clause of the plug statement. The content of each row element is mapped to a separate value collection. Return to the Booklist example in front of this chapter, below is the SQL statement of items that store XML format: Insert Into Booklist (Bookid, Title, Author, Publisher, Year, ISBN, Description) VALUS (?,?,?,?,?,? ?,) and BIND the values, BOOKID -> 1234TITLE -> The Difference Between God and Larry Ellison: Inside Oracle CorporationAUTHOR -> Mike WilsonPUBLISHER -> William Morrow & Co.YEAR -> 1997ISBN -> 0688149251Description -> Account of Larry Ellison; The following example code demonstrates how to complete this feature in the Java program: import oracle.xml.sql.dml. *; Import java.sql. *; Import oracle.jdbc.driver. *; Import oracle.jdbc. *; Import java.net *;. public class save_sample {public static void main (String args []) throw SQLException {Srting tabName = "bOOKLIST"; // table into which to insertString fileName = "samdoc.xml"; // file containing the xml docDriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); Connection conn = DriverManager.getConnection ( "jdbc: oracle: oci8: scott / tiger @"); OracleXMLSave sav = new OracleXMLSave (conn, tabName); URL URL = sav.createurl (filename); int = sav.ins OrtXML (URL); System.out.println ("Success Insert" RowCount "Rows INTO" TabName); conn.close ();}} Like getXML, the storage function also has a command line version called PUTXML. It can be used to load XML data in large numbers. The following command will load an XML document, where the document contains a list of books that meet the example DTD. Java OracleXML PUTXML? User "Scott / Tiger" Sampdoc.xml Booklist</p>
<p>Updata XML Format Data Update Actions can be applied to multi-line in the table, which is different from insertion operations. If the matching column is not a key column in the table, the updated XML element may match multi-line. So update the operation requires a list of keyword columns, where the utility uses the list to determine the row to update. The following bibliographic list update example illustrates this situation: <RowSet> <row number = "1"> <bookid> 1234 </ bookid> <title> The Difference Between God and Larry Ellison: Inside Oracle Corporation </ Title> < Author> Mike Wilson </ Author> <Publisher> William Morrow and Co. </ Publisher> <Year> 1997 </ year> <ISBN> 0688149251 </ ISBN> </ ROW> </ ROWSET></p>
<p>The XML update executes SQL statement that passes the BookID column and keyword column value: update booklist set title =?, Author =? Where bookid =? And bind the value -> the Difference Between god and LARRY Ellison: Inside Oracle Corporation Author -> Mike Wilson; Note that you don't need to update all columns in the initial XML document. The following sample code gives the Java program how to complete this feature:</p>
<p>Import oracle.xml.sql.dml. *; import java.sql. *; import oracle.jdbc.driver. *; import oracle.jdbc. *; public class listupdate {public static void main (String Argv []) THROWS SQLEXCEPTION {String tabName = "bOOKLIST"; // table into which to insertDriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); Connection conn = DriverManager.getConnection ( "jdbc: oracle: oci8: scott / tiger @") ; OracleXMLSave sav = new OracleXMLSave (conn, tabName); String [] keyColNames = new string [1]; keyColNames [0] = "BOOKID"; sav.setKeyColumnNames (keyColNames); sav.updateXML (argv [0]); sav .Close ();}}}</p>
<p>DELETE XML Format Data XML SQL Utility also supports delete operations of an XML document. Like the update operation, the delete operation also uses the keyword column to determine the deleted row. If one or more keywords are not given, the delete operation will still try the columns in the document. Below is the XML document and the corresponding SQL delete statement:</p>
<p><RowSet> <row num = "1"> <bookid> 1234 </ bookid> <title> The Difference Between God and Larry Ellison: Inside Oracle Corporation </ Title> <Author> Mike Wilson </ Author> <Publisher> William Morrow and Co. </ Publisher> <Year> 1997 </ year> <isbn> 0688149251 </ isbn> </ row> </ rowset> delete from booklist whre title =? And author =? And publisher =? And year = ? aND ISBN = aND BOOKID = binding, BOOKID <- 1234 TITLE <-The Difference Between God and Larry Ellison:?? Inside Oracle Corporation AUTHOR <- Mike Wilson PUBLISHER <- William Morrow & Co. YEAR <-1997 ISBN <- 0688149251</p>
<p>The following example shows how Java implements delete operations, where BookId is used as a key list:</p>
<p>Import oracle.xml.sql.dml. *; import java.sql. *; import oracle.jdbc.driver. *; import oracle.jdbc. *;</p>
<p>public class ListDelete {public static void main (String argv []) throws SQLException {String tabName = "BOOKLIST"; // table into which to delete data DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); Connection conn = DriverManager.getConnection (jdbc: oracle: oci8: scott / tiger @ "); OracleXMLSave sav = new OracleXMLSave (conn, tabName); String [] keyColNames = new String [1]; keyColNames [0] =" BookID "; sav .SetKeyColmnnames (KeyColnames); sav.deletexml (Argv [0]); sav.close ();}}</p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-60792.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">
</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="60792" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved
</div><div class="col text-right">Processed:
<b>0.041</b>, SQL:
<b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'Ty8VgnKfq0jKXJuDhfEonrGvptIKJsffjHDEJ80KLC11Ci32_2FzE5UiZQwYsI1fER1C407R_2FsPU8Js_2FXF';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});
function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width();
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}
if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {
});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}
function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>');
});
}
$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});
var jmessage = $('#message');
jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); });
jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); });
$('#nav li[data-active="fid-1"]').addClass('active');
</script>
