{"id":4308,"date":"2013-12-08T17:48:20","date_gmt":"2013-12-08T17:48:20","guid":{"rendered":"http:\/\/erpschools.com\/?p=4308"},"modified":"2014-12-05T10:06:42","modified_gmt":"2014-12-05T10:06:42","slug":"dbms_xmlstore_example_oracle","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle","title":{"rendered":"DBMS_XMLSTORE: Insert, Update, Delete XML data"},"content":{"rendered":"

Overview:<\/span>
\nDBMS_XMLSTORE API \/ Package enables DML operations to be performed on relational tables using XML data.<\/p>\n

Steps to use DBMS_XMLSTORE:<\/span><\/p>\n

1. Create a context handle by calling function DBMS_XMLSTORE.newContext and supplying it with the table name to use for the DML operations. For case sensitivity, double-quote (“) the string that is passed to the function.<\/p>\n

2. By default, XML documents are expected to use the tag to identify rows. This is the same default used by package DBMS_XMLGEN when generating XML data. You can use function setRowTag to override this behavior.<\/p>\n

3. For inserts, to improve performance you can specify the list of columns to insert by calling procedure DBMS_XMLSTORE.setUpdateColumn for each column. The default behavior (if you do not specify the list of columns) is to insert values for each column whose corresponding element is present in the XML document.<\/p>\n

4. For updates, use function DBMS_XMLSTORE.setKeyColumn to specify one or more (pseudo-) key columns, which are used to specify the rows to update. You do this in the WHERE clause of a SQL UPDATE statement. The columns that you specify need not be keys of the table, but together they must uniquely specify the rows to update.
\nFor example, in table employees, column employee_id uniquely identifies rows (it is a key of the table). If the XML document that you use to update the table contains element <EMPLOYEE_ID>2176, then the rows where employee_id equals 2176 are updated.
\nTo improve performance, you can also specify the list of update columns using DBMS_XMLSTORE.setUpdateColumn. The default behavior is to update all of the columns in the row(s) identified by setKeyColumn whose corresponding elements are present in the XML document.<\/p>\n

5. For deletions you specify (pseudo-) key columns to identify the row(s) to delete. You do this the same way you specify rows to update\u2009\u2014\u2009see step 3.<\/p>\n

6. Provide a document to PL\/SQL function insertXML, updateXML, or deleteXML. You can repeat this step to update several XML documents.<\/p>\n

7. Close the context by calling function DBMS_XMLSTORE.closeContext.<\/p>\n

Inserting with DBMS_XMLSTORE:<\/span>
\nSELECT employee_id AS EMP_ID, salary, hire_date, job_id, email, last_name
\n FROM employees WHERE department_id = 30;<\/code>
\nOutput:<\/p>\n

EMP_ID SALARY HIRE_DATE JOB_ID EMAIL LAST_NAME
\n—— ———- ——— ———- ———- ———-
\n114 11000 07-DEC-94 PU_MAN DRAPHEAL Raphaely
\n115 3100 18-MAY-95 PU_CLERK AKHOO Khoo
\n116 2900 24-DEC-97 PU_CLERK SBAIDA Baida
\n117 2800 24-JUL-97 PU_CLERK STOBIAS Tobias
\n118 2600 15-NOV-98 PU_CLERK GHIMURO Himuro
\n119 2500 10-AUG-99 PU_CLERK KCOLMENA Colmenares<\/p>\n

6 rows selected.
\nDECLARE
\n insCtx DBMS_XMLSTORE.ctxType;
\n rows NUMBER;
\n xmlDoc CLOB
\n := '
\n <EMPLOYEE_ID>920
\n 1800
\n <DEPARTMENT_ID>30
\n <HIRE_DATE>17-DEC-2002
\n <LAST_NAME>Satya
\n Venkata
\n <JOB_ID>ST_CLERK
\n <EMPLOYEE_ID>921
\n 2000
\n <DEPARTMENT_ID>30
\n <HIRE_DATE>31-DEC-2004
\n <LAST_NAME>Vaddi
\n Kumar
\n <JOB_ID>ST_CLERK
\n ' ;
\nBEGIN
\n insCtx := DBMS_XMLSTORE.newContext ('HR.EMPLOYEES'); -- Get saved context
\n DBMS_XMLSTORE.clearUpdateColumnList (insCtx); -- Clear the update settings<\/p>\n

-- Set the columns to be updated as a list of values
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'EMPLOYEE_ID');
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'SALARY');
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'HIRE_DATE');
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'DEPARTMENT_ID');
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'JOB_ID');
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'EMAIL');
\n DBMS_XMLSTORE.setUpdateColumn (insCtx, 'LAST_NAME');<\/p>\n

-- Insert the doc.
\n rows := DBMS_XMLSTORE.insertXML (insCtx, xmlDoc);
\n DBMS_OUTPUT.put_line (rows || ' rows inserted.');<\/p>\n

-- Close the context
\n DBMS_XMLSTORE.closeContext (insCtx);
\nEND;
\n\/<\/code><\/p>\n

2 rows inserted.
\nPL\/SQL procedure successfully completed.<\/p>\n

SELECT employee_id AS EMP_ID, salary, hire_date, job_id, email, last_name
\n FROM employees WHERE department_id = 30;<\/code><\/p>\n

EMP_ID SALARY HIRE_DATE JOB_ID EMAIL LAST_NAME
\n—— ———- ——— ———- ———- ———-
\n114 11000 07-DEC-94 PU_MAN DRAPHEAL Raphaely
\n115 3100 18-MAY-95 PU_CLERK AKHOO Khoo
\n116 2900 24-DEC-97 PU_CLERK SBAIDA Baida
\n117 2800 24-JUL-97 PU_CLERK STOBIAS Tobias
\n118 2600 15-NOV-98 PU_CLERK GHIMURO Himuro
\n119 2500 10-AUG-99 PU_CLERK KCOLMENA Colmenares
\n920 1800 17-DEC-02 ST_CLERK Venkata Satya
\n921 2000 31-DEC-04 ST_CLERK Kumar Vaddi<\/p>\n

8 rows selected.<\/p>\n

Updating with DBMS_XMLSTORE:<\/span><\/p>\n

To update (modify) existing data using package DBMS_XMLSTORE, you must specify which rows to update. In SQL, you would do that using a WHERE clause in an UPDATE statement. With DBMS_XMLSTORE, you do it by calling procedure setKeyColumn once for each of the columns that are used collectively to identify the row.<\/p>\n

You can think of this set of columns as acting like a set of key columns: together, they specify a unique row to be updated. However, the columns that you use (with setKeyColumn) need not be keys of the table\u2009\u2014\u2009as long as they uniquely specify a row, they can be used with calls to setKeyColumn.<\/p>\n

SELECT employee_id, first_name
\n FROM employees
\n WHERE employee_id = 188;<\/code><\/p>\n

EMPLOYEE_ID FIRST_NAME
\n———– ———-
\n188 Kelly<\/p>\n

1 row selected.<\/p>\n

DECLARE
\n updCtx DBMS_XMLSTORE.ctxType;
\n rows NUMBER;
\n xmlDoc CLOB
\n := '
\n <EMPLOYEE_ID>188
\n <FIRST_NAME>Dora
\n ' ;
\nBEGIN
\n updCtx := DBMS_XMLSTORE.newContext ('HR.EMPLOYEES'); -- get the context
\n DBMS_XMLSTORE.clearUpdateColumnList (updCtx); -- clear update settings
\n -- Specify that column employee_id is a \"key\" to identify the row to update.
\n DBMS_XMLSTORE.setKeyColumn (updCtx, 'EMPLOYEE_ID');
\n rows := DBMS_XMLSTORE.UPDATEXML (updCtx, xmlDoc); -- update the table
\n DBMS_XMLSTORE.closeContext (updCtx); -- close the context
\nEND;
\n\/
\n<\/code><\/p>\n

SELECT employee_id, first_name
\n FROM employees
\n WHERE employee_id = 188;<\/code><\/p>\n

EMPLOYEE_ID FIRST_NAME
\n———– ———-
\n188 Dora<\/p>\n

1 row selected.<\/p>\n

Deleting with DBMS_XMLSTORE:<\/span>
\nDeletions are treated similarly to updates: you specify the key or pseudo-key columns that identify the rows to delete.<\/p>\n

SELECT employee_id
\n FROM employees
\n WHERE employee_id = 188;<\/code><\/p>\n

EMPLOYEE_ID
\n———–
\n188<\/p>\n

1 row selected.<\/p>\n

DECLARE
\n delCtx DBMS_XMLSTORE.ctxType;
\n rows NUMBER;
\n xmlDoc CLOB
\n := '
\n <EMPLOYEE_ID>188
\n <DEPARTMENT_ID>50
\n ' ;
\nBEGIN
\n delCtx := DBMS_XMLSTORE.newContext ('HR.EMPLOYEES');
\n DBMS_XMLSTORE.setKeyColumn (delCtx, 'EMPLOYEE_ID');
\n rows := DBMS_XMLSTORE.DELETEXML (delCtx, xmlDoc);
\n DBMS_XMLSTORE.closeContext (delCtx);
\nEND;
\n\/<\/code><\/p>\n

SELECT employee_id
\n FROM employees
\n WHERE employee_id = 188;<\/code><\/p>\n

no rows selected.<\/p>\n","protected":false},"excerpt":{"rendered":"

Overview: DBMS_XMLSTORE API \/ Package enables DML operations to be performed on relational tables using XML data. Steps to use DBMS_XMLSTORE: 1. Create a context handle by calling function DBMS_XMLSTORE.newContext and supplying it with the table name to use for the DML operations. For case sensitivity, double-quote (“) the string that is passed to the […]<\/p>\n","protected":false},"author":18,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[500],"tags":[150,151,152,153,125,154],"class_list":["post-4308","post","type-post","status-publish","format-standard","hentry","category-sysadmin-api","tag-dbms_xmlstore","tag-delete","tag-example","tag-insert","tag-script","tag-update"],"yoast_head":"\nDBMS_XMLSTORE: Insert, Update, Delete XML data - erpSchools<\/title>\n<meta name=\"description\" content=\"dbms_xmlstore script with example\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"satyakumarvaddi\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#article\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle\"},\"author\":{\"name\":\"satyakumarvaddi\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/101b87ea29f633b61a6508ae4942ca69\"},\"headline\":\"DBMS_XMLSTORE: Insert, Update, Delete XML data\",\"datePublished\":\"2013-12-08T17:48:20+00:00\",\"dateModified\":\"2014-12-05T10:06:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle\"},\"wordCount\":633,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"keywords\":[\"DBMS_XMLSTORE\",\"delete\",\"example\",\"insert\",\"script\",\"update\"],\"articleSection\":[\"Sysadmin API\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle\",\"url\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle\",\"name\":\"DBMS_XMLSTORE: Insert, Update, Delete XML data - erpSchools\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#website\"},\"datePublished\":\"2013-12-08T17:48:20+00:00\",\"dateModified\":\"2014-12-05T10:06:42+00:00\",\"description\":\"dbms_xmlstore script with example\",\"breadcrumb\":{\"@id\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/erpschools.com\/erps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DBMS_XMLSTORE: Insert, Update, Delete XML data\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/erpschools.com\/erps\/#website\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"name\":\"erpSchools\",\"description\":\"Oracle Apps\",\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/erpschools.com\/erps\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\",\"name\":\"erpSchools\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"contentUrl\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"width\":250,\"height\":60,\"caption\":\"erpSchools\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/facebook.com\/erpschools\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/101b87ea29f633b61a6508ae4942ca69\",\"name\":\"satyakumarvaddi\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6ebc0692b75cd7ade701de8a48dfc15c7ff745f1a8f0f04061dea4dae764843d?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6ebc0692b75cd7ade701de8a48dfc15c7ff745f1a8f0f04061dea4dae764843d?s=96&d=blank&r=g\",\"caption\":\"satyakumarvaddi\"},\"url\":\"https:\/\/erpschools.com\/erps\/author\/satyakumarvaddi\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DBMS_XMLSTORE: Insert, Update, Delete XML data - erpSchools","description":"dbms_xmlstore script with example","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle","twitter_misc":{"Written by":"satyakumarvaddi","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#article","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle"},"author":{"name":"satyakumarvaddi","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/101b87ea29f633b61a6508ae4942ca69"},"headline":"DBMS_XMLSTORE: Insert, Update, Delete XML data","datePublished":"2013-12-08T17:48:20+00:00","dateModified":"2014-12-05T10:06:42+00:00","mainEntityOfPage":{"@id":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle"},"wordCount":633,"commentCount":1,"publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"keywords":["DBMS_XMLSTORE","delete","example","insert","script","update"],"articleSection":["Sysadmin API"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#respond"]}]},{"@type":"WebPage","@id":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle","url":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle","name":"DBMS_XMLSTORE: Insert, Update, Delete XML data - erpSchools","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/#website"},"datePublished":"2013-12-08T17:48:20+00:00","dateModified":"2014-12-05T10:06:42+00:00","description":"dbms_xmlstore script with example","breadcrumb":{"@id":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/erpschools.com\/erps\/api\/sysadmin-api\/dbms_xmlstore_example_oracle#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/erpschools.com\/erps"},{"@type":"ListItem","position":2,"name":"DBMS_XMLSTORE: Insert, Update, Delete XML data"}]},{"@type":"WebSite","@id":"https:\/\/erpschools.com\/erps\/#website","url":"https:\/\/erpschools.com\/erps\/","name":"erpSchools","description":"Oracle Apps","publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/erpschools.com\/erps\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/erpschools.com\/erps\/#organization","name":"erpSchools","url":"https:\/\/erpschools.com\/erps\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/","url":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","contentUrl":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","width":250,"height":60,"caption":"erpSchools"},"image":{"@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/facebook.com\/erpschools"]},{"@type":"Person","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/101b87ea29f633b61a6508ae4942ca69","name":"satyakumarvaddi","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6ebc0692b75cd7ade701de8a48dfc15c7ff745f1a8f0f04061dea4dae764843d?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6ebc0692b75cd7ade701de8a48dfc15c7ff745f1a8f0f04061dea4dae764843d?s=96&d=blank&r=g","caption":"satyakumarvaddi"},"url":"https:\/\/erpschools.com\/erps\/author\/satyakumarvaddi"}]}},"_links":{"self":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4308","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/users\/18"}],"replies":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/comments?post=4308"}],"version-history":[{"count":0,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4308\/revisions"}],"wp:attachment":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/media?parent=4308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/categories?post=4308"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/tags?post=4308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}