| General Information |
| Note: Used to insert, update, and delete relational data with mapping of XML tag names to relational column names. |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsxml.sql |
| Data Types |
-- context handles
SUBTYPE ctxHandle IS NUMBER;
SUBTYPE ctxType IS NUMBER;
SUBTYPE conversionType IS NUMBER; |
| Dependencies |
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
|
| |
| CLEARKEYCOLUMNLIST |
| Clears the current key column list if one exists |
dbms_xmlstore.clearKeyColumnList(ctxHdl IN ctxType); |
| TBD |
| |
| CLEARUPDATECOLUMNLIST |
| Clears the current update column list if one exists |
dbms_xmlstore.clearUpdateColumnList(ctxHdl IN ctxType); |
| See Demo at page bottom |
| |
| CLOSECONTEXT |
| Closes the context based on its handle |
dbms_xmlstore.closeContext(ctxHdl IN ctxHandle); |
| See Demo at page bottom |
| |
| DELETEXML |
Deletes the specified records from the XML document
Overload 1 |
dbms_xmlstore.deleteXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER; |
| TBD |
| Overload 2 |
dbms_xmlstore.deleteXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER; |
| TBD |
| Overload 3 |
dbms_xmlstore.deleteXML(ctxHdl IN ctxType, xDoc IN XMLTYPE) RETURN NUMBER; |
| TBD |
| |
| INSERTXML |
Inserts the XML document into a table
Overload 1 |
dbms_xmlstore.insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER; |
| See Demo at page bottom |
| Overload 2 |
dbms_xmlstore.insertXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER; |
| See Demo at page bottom |
| Overload 3 |
dbms_xmlstore.insertXML(ctxHdl IN ctxType, xDoc IN XMLTYPE) RETURN NUMBER; |
| TBD |
| |
| NEWCONTEXT |
| Creates a save context, and returns the context's handle |
dbms_xmlstore.newContext(targetTable IN VARCHAR2) RETURN ctxHandle; |
| See Demo at page bottom |
| |
| SETKEYCOLUMN |
| Adds a column to the key column list |
dbms_xmlstore.setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2); |
| See Demo at page bottom |
| |
| SETROWTAG |
| Names the tag used in the XML document that enclose the XML elements to be referenced |
dbms_xmlstore.setRowTag(ctx IN ctxHandle, rowTagName IN VARCHAR2); |
| TBD |
| |
| SETUPDATECOLUMN |
| Adds a column to the update column list |
dbms_xmlstore.setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2); |
| See Demo at page bottom |
| |
| UPDATEXML |
Updates the table for the specified XML document
Overload 1 |
dbms_xmlstore.updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER; |
| See Demo at page bottom |
| Overload 2 |
dbms_xmlstore.updateXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER; |
| See Demo at page bottom |
| Overload 3 |
dbms_xmlstore.updateXML(ctxHdl IN ctxType, xDoc IN XMLTYPE) RETURN NUMBER; |
| TBD |
| |
| Package Demo |
| This demo shows usage of multiple pieces of DBMS_XMLSTORE functionality |
conn hr/hr
CREATE TABLE xmlstore_tab AS
SELECT *
FROM employees
WHERE 1=2;
DECLARE
insCtx DBMS_XMLSTORE.ctxType;
rows NUMBER;
xmlDoc CLOB := '<ROWSET>
<ROW num="1">
<EMPLOYEE_ID>998</EMPLOYEE_ID>
<SALARY>1000</SALARY>
<DEPARTMENT_ID>42</DEPARTMENT_ID>
<HIRE_DATE>07-JUL-2011</HIRE_DATE>
<LAST_NAME>Morgan</LAST_NAME>
<EMAIL>c-dmorgan</EMAIL>
<JOB_ID>DBA</JOB_ID>
</ROW>
<ROW>
<EMPLOYEE_ID>999</EMPLOYEE_ID>
<SALARY>2000</SALARY>
<DEPARTMENT_ID>24</DEPARTMENT_ID>
<HIRE_DATE>01-MAY-2011</HIRE_DATE>
<LAST_NAME>Manfredi</LAST_NAME>
<EMAIL>wmanfredi</EMAIL>
<JOB_ID>DEVELOPER</JOB_ID>
</ROW>
</ROWSET>';
BEGIN
insCtx := dbms_xmlstore.newContext('hr.xmlstore_tab');
dbms_xmlstore.clearUpdateColumnList(insCtx); -- clear previous settings
-- set columns to update
dbms_xmlstore.setUpdateColumn(insCtx, 'EMPLOYEE_ID');
dbms_xmlstore.setUpdateColumn(insCtx, 'SALARY');
dbms_xmlstore.setUpdateColumn(insCtx, 'HIRE_DATE');
dbms_xmlstore.setUpdateColumn(insCtx, 'DEPARTMENT_ID');
dbms_xmlstore.setUpdateColumn(insCtx, 'JOB_ID');
dbms_xmlstore.setUpdateColumn(insCtx, 'EMAIL');
dbms_xmlstore.setUpdateColumn(insCtx, 'LAST_NAME');
-- insert the XML doc
rows := dbms_xmlstore.insertXML(insCtx, xmlDoc);
dbms_output.put_line(rows || ' row count');
-- close the context
dbms_xmlstore.closeContext(insCtx);
END;
/
SELECT employee_id, first_name, last_name
FROM xmlstore_tab;
DECLARE
updCtx dbms_xmlstore.ctxType;
rows NUMBER;
xmlDoc CLOB := '<ROWSET>
<ROW>
<EMPLOYEE_ID>998</EMPLOYEE_ID>
<FIRST_NAME>Dan</FIRST_NAME>
</ROW>
</ROWSET>';
BEGIN
updCtx := dbms_xmlstore.newContext('HR.XMLSTORE_TAB');
dbms_xmlstore.clearUpdateColumnList(updCtx);
-- identify employee_id is the "key" to identify the row to update
dbms_xmlstore.setKeyColumn(updCtx, 'EMPLOYEE_ID');
rows := dbms_xmlstore.updateXML(updCtx, xmlDoc); -- update the table
dbms_xmlstore.closeContext(updCtx); -- close the context
END;
/
SELECT employee_id, first_name, last_name
FROM xmlstore_tab; |
