Home > HowTo, Miscellaneous, XQuery > How To : Update XML nodes with values from the same document

How To : Update XML nodes with values from the same document

This post was inspired by a recent thread on OTN where I suggested a few methods to update one or multiple nodes with values from the same document.

Let’s consider this simple structure :

<workbook>
 <worksheet sheetName="MySheet" sheetId="1"/>
 <worksheet sheetName="MySheet" sheetId="2"/>
 <worksheet sheetName="MySheet" sheetId="3"/>
</workbook>

and that we want to update the sheetName of each worksheet using the value in sheetId.

 

1. The old technique : updateXML()

The SQL function updateXML() is simple to use, there are three mandatory parameters : target document, target node (XPath), new value.
However, the new value cannot be correlated to the target node in any way. For example, we can’t directly do @sheetId = @sheetId + 1, or @sheetName = concat(“NEW_”, @sheetName).

New values derived from the document itself have to be extracted first and assigned to the target node(s) in a loop :

DECLARE

  doc  xmltype := xmltype(
  '<workbook>
 <worksheet sheetName="MySheet" sheetId="1"/>
 <worksheet sheetName="MySheet" sheetId="2"/>
 <worksheet sheetName="MySheet" sheetId="3"/>
</workbook>');

BEGIN
 
  for r in (
    select sheet_name || sheet_id as new_name
         , to_char(rn)            as idx
    from xmltable('/workbook/worksheet' 
                  passing doc
                  columns rn         for ordinality
                        , sheet_id   number       path '@sheetId'
                        , sheet_name varchar2(30) path '@sheetName')
  )
  loop
   
    select updatexml( doc
                    , '/workbook/worksheet['||r.idx||']/@sheetName'
                    , r.new_name )
    into doc
    from dual;
    
  end loop;

  dbms_output.put_line('Updated document : ');
  dbms_output.put_line(doc.getclobval());
  
END;
/

Updated document : 
<workbook><worksheet sheetName="MySheet1" sheetId="1"/><worksheet sheetName="MySheet2" sheetId="2"/><worksheet sheetName="MySheet3" sheetId="3"/></workbook>

 

2. Xdiff interface and the XMLPatch() function

Starting with release 11.1, the Oracle XDK provides a C implementation of an XML difference (Xdiff) algorithm : Determining XML Differences Using C.

– XmlDiff function compares two input XML trees and returns an Xdiff instance document describing the differences (if any) in the form of update, delete or insert primitives.

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

Now comes the interesting part : XmlDiff and XmlPatch are also exposed as SQL functions.
Here, we’ll use XMLPatch() to apply a custom Xdiff document generated from the data we want to update. It consists in three update-node elements each targeting the sheetName attribute :

<xd:xdiff xmlns:xd="http://xmlns.oracle.com/xdb/xdiff.xsd" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
          xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd">
  <?oracle-xmldiff operations-in-docorder="true" output-model="current"?>
  <xd:update-node xd:node-type="attribute" xd:xpath="/workbook/worksheet[1]" xd:attr-local="sheetName">
    <xd:content>MySheet1</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="attribute" xd:xpath="/workbook/worksheet[2]" xd:attr-local="sheetName">
    <xd:content>MySheet2</xd:content>
  </xd:update-node>
  <xd:update-node xd:node-type="attribute" xd:xpath="/workbook/worksheet[3]" xd:attr-local="sheetName">
    <xd:content>MySheet3</xd:content>
  </xd:update-node>
</xd:xdiff>

It can be built entirely with XQuery from the source document and applied via XMLPatch in the same query :

SQL> create table tmp_xml of xmltype;
 
Table created
 
SQL> insert into tmp_xml values (
  2    xmltype('<workbook>
  3   <worksheet sheetName="MySheet" sheetId="1"/>
  4   <worksheet sheetName="MySheet" sheetId="2"/>
  5   <worksheet sheetName="MySheet" sheetId="3"/>
  6  </workbook>')
  7  );
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select xmlserialize(document
  2           xmlpatch(
  3             object_value
  4           , xmlquery(
  5             'declare namespace xd = "http://xmlns.oracle.com/xdb/xdiff.xsd"; (::)
  6              element xd:xdiff
  7              {
  8                attribute              xsi:schemaLocation { "http://xmlns.oracle.com/xdb/xdiff.xsd http://xmlns.oracle.com/xdb/xdiff.xsd" }
  9              , processing-instruction oracle-xmldiff     { ''operations-in-docorder="true" output-model="current"'' }
 10              , for $i at $p in /workbook/worksheet
 11                return element xd:update-node
 12                {
 13                  attribute xd:node-type  { "attribute" }
 14                , attribute xd:xpath      { concat("/workbook/worksheet[", $p, "]") }
 15                , attribute xd:attr-local { "sheetName" }
 16                , element   xd:content    { concat($i/@sheetName, $i/@sheetId) }
 17                }
 18              }'
 19             passing object_value
 20             returning content
 21             )
 22           )
 23           as clob indent
 24         )
 25  from tmp_xml
 26  ;
 
XMLSERIALIZE(DOCUMENTXMLPATCH(
--------------------------------------------------------------------------------
<workbook>
  <worksheet sheetName="MySheet1" sheetId="1"/>
  <worksheet sheetName="MySheet2" sheetId="2"/>
  <worksheet sheetName="MySheet3" sheetId="3"/>
</workbook>
 

 

3. XQuery Update Facility

XQuery Update Facility (XQUF) is a small extension to the XQuery language. It introduces a few primitives to easily modify an XML document : updating a scalar value (attribute or text node), updating an entire element, inserting or deleting nodes, etc.
XQUF is available in Oracle database starting with version 11.2.0.3 via the usual XQuery functions XMLQuery() and XMLTable().
I’ll probably write an article soon about using XQUF in the database, so I won’t go into the details right now.

Applied to the present case, it should be as simple as :

SQL> select xmlquery(
  2           'copy $d := .
  3            modify (
  4              for $i in $d/workbook/worksheet
  5              return replace value of node $i/@sheetName with concat($i/@sheetName, $i/@sheetId)
  6            )
  7            return $d'
  8           passing t.object_value
  9           returning content
 10         ) as updated_doc
 11  from tmp_xml t
 12  ;
ERROR:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
- got multi-item sequence



no rows selected

However, there’s a bug (apparently) related to XQuery rewrite in this case and we have to force functional evaluation to make it work as expected :

SQL> select /*+ no_xml_query_rewrite */
  2         xmlquery(
  3           'copy $d := .
  4            modify (
  5              for $i in $d/workbook/worksheet
  6              return replace value of node $i/@sheetName with concat($i/@sheetName, $i/@sheetId)
  7            )
  8            return $d'
  9           passing t.object_value
 10           returning content
 11         ) as updated_doc
 12  from tmp_xml t
 13  ;

UPDATED_DOC
--------------------------------------------------------------------------------
<workbook>
  <worksheet sheetName="MySheet1" sheetId="1"/>
  <worksheet sheetName="MySheet2" sheetId="2"/>
  <worksheet sheetName="MySheet3" sheetId="3"/>
</workbook>

Here’s the complete test case posted on OTN when I first encountered the problem : XQuery Update Facility : replacing value of multiple nodes gives ORA-01427.

 

4. Using schema-based XMLType and Object-Relational storage

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. 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 :

SQL> BEGIN
  2   dbms_xmlschema.registerSchema(
  3     schemaURL => 'workbook.xsd'
  4   , schemaDoc =>
  5  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
  6    <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET" xdb:maintainDOM="false">
  7      <xs:attribute name="sheetName" type="xs:string"/>
  8      <xs:attribute name="sheetId" type="xs:integer"/>
  9    </xs:complexType>
 10    <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
 11      <xs:sequence>
 12        <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
 13      </xs:sequence>
 14    </xs:complexType>
 15    <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/>
 16  </xs:schema>'
 17   , local => true
 18   , genTypes => true
 19   , genTables => true
 20   , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
 21   );
 22  END;
 23  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2   dbms_xmlstorage_manage.renameCollectionTable(
  3     tab_name => 'WORKBOOK_XML'
  4   , xpath => '"XMLDATA"."worksheet"'
  5   , collection_table_name => 'WORKSHEET_XML'
  6   );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> insert into workbook_xml values(
  2   xmlparse(document '<workbook>
  3   <worksheet sheetName="MySheet" sheetId="1"/>
  4   <worksheet sheetName="MySheet" sheetId="2"/>
  5   <worksheet sheetName="MySheet" sheetId="3"/>
  6  </workbook>')
  7  );

1 row created.

SQL> commit;

Commit complete.

SQL> col sheetName format a30
SQL> col sheetId format 99
SQL> set lines 120
SQL> set autotrace on explain
SQL> select x.*
  2  from workbook_xml t
  3     , xmltable( '/workbook/worksheet' passing t.object_value
  4                 columns sheetName varchar2(4000) path '@sheetName'
  5                       , sheetId   number         path '@sheetId' ) x
  6  ;

SHEETNAME                      SHEETID
------------------------------ -------
MySheet                              1
MySheet                              2
MySheet                              3


Execution Plan
----------------------------------------------------------
Plan hash value: 3765576126

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     3 |   135 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                       |       |       |            |          |
|   2 |   NESTED LOOPS               |                       |     3 |   135 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | WORKSHEET_XML_MEMBERS |     1 |    17 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | SYS_C007585           |     3 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| WORKSHEET_XML         |     3 |    84 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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 "sheetName" = "sheetName" || "sheetId"
  6  ;

3 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 809584677

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |               |     3 |    84 |     5   (0)| 00:00:01 |
|   1 |  UPDATE             | WORKSHEET_XML |       |       |            |          |
|*  2 |   INDEX RANGE SCAN  | SYS_C007585   |     3 |    84 |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| WORKBOOK_XML  |     1 |    17 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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> set autotrace off
SQL> select x.*
  2  from workbook_xml t
  3     , xmltable( '/workbook/worksheet' passing t.object_value
  4                 columns sheetName varchar2(4000) path '@sheetName'
  5                       , sheetId   number         path '@sheetId' ) x
  6  ;

SHEETNAME                      SHEETID
------------------------------ -------
MySheet1                             1
MySheet2                             2
MySheet3                             3

That is by far the most efficient method, but again, not supported.

 

5. Using DOM over XMLType

DECLARE

  doc      xmltype := xmltype(
  '<workbook>
 <worksheet sheetName="MySheet" sheetId="1"/>
 <worksheet sheetName="MySheet" sheetId="2"/>
 <worksheet sheetName="MySheet" sheetId="3"/>
</workbook>');

  DOMDoc   dbms_xmldom.DOMDocument;
  docNode  dbms_xmldom.DOMNode;
  
  nodeList    dbms_xmldom.DOMNodeList;
  itemNode    dbms_xmldom.DOMNode;
  targetNode  dbms_xmldom.DOMNode;

BEGIN
  
  DOMDoc := dbms_xmldom.newDOMDocument(doc);
  docNode := dbms_xmldom.makeNode(DOMDoc);
 
  nodeList := dbms_xslprocessor.selectNodes(docNode, '/workbook/worksheet');

  for i in 0 .. dbms_xmldom.getLength(nodeList) - 1 loop

    itemNode := dbms_xmldom.item(nodeList, i);
    targetNode := dbms_xslprocessor.selectSingleNode(itemNode, './@sheetName');
    
    dbms_xmldom.setNodeValue(
      targetNode
    , dbms_xmldom.getNodeValue(targetNode) || 
      dbms_xslprocessor.valueOf(itemNode, './@sheetId')
    );

  end loop;

  dbms_xmldom.freeDocument(DOMDoc);
  
  dbms_output.put_line('Updated document : ');
  dbms_output.put_line(doc.getclobval());
  
END;
/

Advertisements
  1. Anonymous
    November 14, 2016 at 17:46

    Thank you, that was very helpfull.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s