DBMS_XMLSTORE
来源:互联网 发布:免费医疗软件 编辑:程序博客网 时间:2024/04/30 23:18
DBMS_XMLSTORE
This chapter introduces you to the PL/SQL package DBMS_XMLSTORE
. This package is used to insert, update, and delete data from XML documents in object-relational tables.
This chapter contains these topics:
Overview of DBMS_XMLSTORE
Using DBMS_XMLSTORE
Insert Processing with DBMS_XMLSTORE
Update Processing with DBMS_XMLSTORE
Delete Processing with DBMS_XMLSTORE
Overview of DBMS_XMLSTORE
The DBMS_XMLSTORE
package enables DML operations to be performed on relational tables using XML. It takes a canonical XML mapping, similar to the one produced by DBMS_XMLGEN
, converts it to object relational constructs, and inserts, updates or deletes the value from relational tables.
The functionality of the DBMS_XMLSTORE
package is similar to that of the DBMS_XMLSAVE
package which is part of the Oracle XML SQL Utility. There are, however, several key differences: DBMS_XMLSTORE
is written in C and compiled into the kernel and hence provides higher performance.
DBMS_XMLSTORE
uses SAX to parse the input XML document and hence has higher scalability and lower memory requirements.DBMS_XMLSTORE
allows input ofXMLType
in addition toCLOB
s andVARCHAR.
While
DBMS_XMLSAVE
is a wrapper around a Java class,DBMS_XMLSTORE
is implemented in C inside the database. This should significantly improve performance.DBMS_XMLSTORE
uses SAX parsing of the incoming XML documents, which provides much greater scalability than the DOM parsing used inDBMS_XMLSAVE
.The
insertXML()
,updateXML()
, anddeleteXML()
functions, which are also present inDBMS_XMLSAVE
, have been enhanced inDBMS_XMLSTORE
to takeXMLTypes
in addition to CLOBs and strings. This provides for better integration with Oracle XML DB functionality.
Using DBMS_XMLSTORE
To use DBMS_XMLSTORE
follow these steps:
Create a context handle by calling the
DBMS_XMLSTORE.newContext()
function and supplying it with the table name to use for the DML operations. For case sensitivity, double-quote the string which is passed to the function.By default, XML documents are expected to identify rows with the <ROW> tag. This is the same default used by
DBMS_XMLGEN
when generating XML. This may be overridden by calling the setRowTag function.For Inserts: You can set the list of columns to insert using the
setUpdateColumn
function for each column. This is highly recommended since it will improve performance. The default is to insert values for all the columns whose corresponding elements are present in the XML document.For Updates: You must specify one or more key columns using the
setKeyColumn
function. The key columns are used to specify which rows are to be updated, like the where clause in a SQL update statement. For example, if you setEMPLOYEE_ID
as a key column, and the XML document contains "<EMPLOYEE_ID>2176</EMPLOYEE_ID>
", then rows whereEMPLOYEE_ID
equals 2176 are updated. The list of update columns can also be specified and is recommended for performance. The default is to update all the columns whose corresponding elements are present in the XML document.For Deletes: Key columns may be set to specify which columns are used for the where clause. The default is for all columns present to be used. Specifying the columns is recommended for performance.
Provide a document to one of
insertXML
,updateXML
, ordeleteXML
.This last step may be repeated multiple times, with several XML documents.
Close the context with the
closeContext
function.
Insert Processing with DBMS_XMLSTORE
To insert an XML document into a table or view, simply supply the table or the view name and then the document. DBMS_XMLSTORE
parses the document and then creates an INSERT statement into which it binds all the values. By default, DBMS_XMLSTORE
inserts values into all the columns represented by elements in the XML document. The following example shows you how the XML document generated from the Employees table, can be stored in the table with relative ease.
Example 11-1 Inserting data with specified columns
DECLARE insCtx DBMS_XMLStore.ctxType; rows NUMBER; xmldoc CLOB := '<ROWSET> <ROW num="1"> <EMPNO>7369</EMPNO> <SAL>1800</SAL> <HIREDATE>27-AUG-1996</HIREDATE> </ROW> <ROW> <EMPNO>2290</EMPNO> <SAL>2000</SAL> <HIREDATE>31-DEC-1992</HIREDATE> </ROW> </ROWSET>';BEGIN insCtx := DBMS_XMLStore.newContext('scott.emp'); -- get saved context DBMS_XMLStore.clearUpdateColumnList(insCtx); -- clear the update settings -- set the columns to be updated as a list of values DBMS_XMLStore.setUpdateColumn(insCtx,'EMPNO'); DBMS_XMLStore.setUpdateColumn(insCtx,'SAL'); DBMS_XMLStore.setUpdatecolumn(insCtx,'HIREDATE'); -- Now insert the doc. -- This will only insert into EMPNO, SAL and HIREDATE columns rows := DBMS_XMLStore.insertXML(insCtx, xmlDoc); -- Close the context DBMS_XMLStore.closeContext(insCtx); END;/
Update Processing with DBMS_XMLSTORE
Now that you know how to insert values into the table from XML documents, let us see how to update only certain values. If you get an XML document to update the salary of an employee and also the department that she works in:
<ROWSET> <ROW num="1"> <EMPNO>7369</EMPNO> <SAL>1800</SAL> <DEPTNO>30</DEPTNO> </ROW> <ROW> <EMPNO>2290</EMPNO> <SAL>2000</SAL> <HIRE_DATE>31-DEC-1992</HIRE_DATE> <!-- additional rows ... --></ROWSET>
you can call the update processing to update the values. In the case of update, you need to supply the list of key column names. These form part of the WHERE clause in the UPDATE statement. In the employees
table shown earlier, the employee number EMPLOYEE_ID
column forms the key that you use for updates.
Example 11-2 Updating Data With Key Columns
Consider the following PL/SQL procedure:
CREATE OR REPLACE PROCEDURE testUpdate (xmlDoc IN CLOB) IS updCtx DBMS_XMLStore.ctxType; rows NUMBER;BEGIN updCtx := DBMS_XMLStore.newContext('scott.emp'); -- get the context DBMS_XMLStore.clearUpdateColumnList(updCtx); -- clear the update settings DBMS_XMLStore.setKeyColumn(updCtx,'EMPNO'); -- set EMPNO as key column rows := DBMS_XMLStore.updateXML(updCtx,xmlDoc); -- update the table DBMS_XMLStore.closeContext(updCtx); -- close the contextEND;/
In this example, when the procedure is executed with a CLOB value that contains the document described earlier, two UPDATE statements are generated. For the first ROW
element, you would generate an UPDATE statement to update the SALARY
and JOB_ID
fields as follows:
UPDATE scott.emp SET SAL = 1800 AND DEPTNO = 30 WHERE EMPNO = 7369;
and for the second ROW
element:
UPDATE scott.emp SET SAL = 2000 AND HIREDATE = 12/31/1992 WHERE EMPNO = 2290;
Delete Processing with DBMS_XMLSTORE
For deletes, you can set the list of key columns. These columns are used in the WHERE clause of the DELETE statement. If the key column names are not supplied, then a new DELETE statement is created for each ROW
element of the XML document where the list of columns in the WHERE clause of the DELETE matches those in the ROW
element.
Example 11-3 Simple deleteXML Example
Consider the following PL/SQL example:
CREATE OR REPLACE PROCEDURE testDelete(xmlDoc IN CLOB) IS delCtx DBMS_XMLStore.ctxType; rows NUMBER;BEGIN delCtx := DBMS_XMLStore.newContext('scott.emp'); DBMS_XMLStore.setKeyColumn(delCtx,'EMPNO'); rows := DBMS_XMLStore.deleteXML(delCtx, xmlDoc); DBMS_XMLStore.closeContext(delCtx);END;/
If you use the same XML document as in the preceding update example, you end up with the following two DELETE
statements:
DELETE FROM scott.emp WHERE EMPNO=7369;DELETE FROM scott.emp WHERE EMPNO=2200;
The DELETE
statements are formed based on the tag names present in each ROW
element in the XML document.
- DBMS_XMLSTORE
- EBMIDE——表单、报表模板设计
- C语言深度解剖【第一章问题】
- 导入/导出Excel
- MS-VC 使用MAP文件快速定位错误行
- NSManagedObject实例的值copy
- DBMS_XMLSTORE
- C++ - Friend友元能继承吗
- 移植thttpd嵌入式web服务器(转)
- 开发者必备的SQL性能优化基本功
- httpservletrequest cannot be resolved to a type
- SQL Server 2008 定时作业的制定(SQL2005参考此方法)
- Chrome源代码分析之进程和线程模型(四)
- oracle xml处理
- ASP.NET MVC Action以强类型的方式接收表单数据