Xquery updating

– Xml Patch function applies the changes described in an Xdiff instance and returns the modified input document.

Now comes the interesting part : Xml Diff and Xml Patch are also exposed as SQL functions.

Execution Plan ---------------------------------------------------------- Plan hash value: 809584677 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 3 | 84 | 5 (0)| | | 1 | UPDATE | WORKSHEET_XML | | | | | |* 2 | INDEX RANGE SCAN | SYS_C007585 | 3 | 84 | 1 (0)| | | 3 | TABLE ACCESS FULL| WORKBOOK_XML | 1 | 17 | 3 (0)| | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("WORKSHEET_XML"."NESTED_TABLE_ID"= SYS_OP_ENFORCE_NOT_NULL$ (SELECT "T"."SYS_NC0000800009$" FROM "WORKBOOK_XML" "T")) SQL select x.* 2 from workbook_xml t 3 , xmltable( '/workbook/worksheet' passing t.object_value 4 columns sheet Name varchar2(4000) path '@sheet Name' 5 , sheet Id number path '@sheet Id' ) x 6 ; SHEETNAME SHEETID ------------------------------ ------- My Sheet1 1 My Sheet2 2 My Sheet3 3 '); DOMDoc dbms_xmldom.

xquery updating-17

With Object-Relational storage, we can also access the underlying structure and update it directly, without involving any XML-related functions.

However, although it’s technically possible, Oracle does not support it officially for DML operations. DOMNode; BEGIN DOMDoc := dbms_DOMDocument(doc); doc Node := dbms_Node(DOMDoc); node List := dbms_xslprocessor.select Nodes(doc Node, '/workbook/worksheet'); for i in 0 ..

No, not really new, XQuery Update (W3C standard/draft 2011) was already implemented in 11.2.0.3.0, but is now officially also announced.

Besides the XQuery Full Text support (XQFT for short, W3C standard/draft 2011), this is one of the big new features in Oracle 12c.

See the note at the bottom of this example : Example 4-16 Object Relational Equivalent of UPDATEXML Expression.

Here’s the complete test case applied on our sample data : select x.* 2 from workbook_xml t 3 , xmltable( '/workbook/worksheet' passing t.object_value 4 columns sheet Name varchar2(4000) path '@sheet Name' 5 , sheet Id number path '@sheet Id' ) x 6 ; SHEETNAME SHEETID ------------------------------ ------- My Sheet 1 My Sheet 2 My Sheet 3 Execution Plan ---------------------------------------------------------- Plan hash value: 3765576126 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 135 | 3 (0)| | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 3 | 135 | 3 (0)| | | 3 | INDEX FULL SCAN | WORKSHEET_XML_MEMBERS | 1 | 17 | 1 (0)| | |* 4 | INDEX RANGE SCAN | SYS_C007585 | 3 | | 1 (0)| | | 5 | TABLE ACCESS BY INDEX ROWID| WORKSHEET_XML | 3 | 84 | 2 (0)| | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("NESTED_TABLE_ID"="T"."SYS_NC0000800009$") SQL update table( 2 select t.xmldata."worksheet" 3 from workbook_xml t 4 ) 5 set "sheet Name" = "sheet Name" Tags: , ,