CODEWITHSUNDEEP
oracle-databasexmltype
Michael
Michael

Reputation: 3256

Update value of a node, ignore if not exists

I would like to replace a value of a node in XML. The XML is stored in Oracle 12.2 database, in an XMLTYPE column. My XML:

<Warehouse>
  <WarehouseId>1</WarehouseId>
  <WarehouseName>Southlake, Texas</WarehouseName>
  <Building>Owned</Building>
  <Area>25000</Area>
</Warehouse>

The UPDATEXML function does the job, but it is slow.

select
UPDATEXML(myxmlcolumn, '/Warehouse/Building/text()','mynewvalue')
from mytable;

Oracle say that UPDATEXML is deprecated, and XMLQUERY should be used instead. So , I have tried XMLQUERY instead:

select
 XMLQUERY(
'copy $t := $x modify(
  replace value of node $t/Warehouse/Building with "mynewvalue"
) return $t'
from mytable;

It works much faster, but there is one little problem: if the requested node does not exist, it fails with XVM-01155: [XUDY0027] Invalid target expression

For example, this select fails with the above error(note the ZZZ fake node name):

select
 XMLQUERY(
'copy $t := $x modify(
  replace value of node $t/Warehouse/ZZZ with "mynewvalue"
) return $t'
from mytable;

Question: How can I change the code to ignore non-existent nodes?

Upvotes: 0

Views: 1094

Answers (2)

Michael
Michael

Reputation: 3256

Following the great answer of @Arkadiusz Łukasiewicz, I have compiled full solution which includes:

  • ignoring non-existent nodes
  • ability to change multiple nodes in a single xmlquery call

Here it goes:

select
    xmlquery(
'    copy $t := $x modify
        (
          (for $i in $t/Warehouse/Building
             return replace value of node $i with "aaa"),
          (for $i in $t/Warehouse/ZZZ
             return replace value of node $i with "bbb)
        )   
    return $t 
'
passing 
    XMLRECORD as "x"  
from mytable

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

IF-ELSE statement can be helpful :) Check example. 

    with mytable as (select xmltype('<Warehouse>
      <WarehouseId>1</WarehouseId>
      <WarehouseName>Southlake, Texas</WarehouseName>
      <Building>Owned</Building>
      <Area>25000</Area>
    </Warehouse>') myxmlcolumn from dual) 
    select 

     XMLQUERY(
    'copy $t := . modify(
     if( $t/Warehouse/WarehouseName) then  
      (
        replace value of node $t/Warehouse/WarehouseName with "mynewvalue"
      )
      else ()
    ) return $t' passing myxmlcolumn returning content)

    from mytable
     union all 
     select 

     XMLQUERY(
    'copy $t := . modify(
     if( $t/Warehouse/ZZZZ) then  
      (
        replace value of node $t/Warehouse/ZZZZ with "mynewvalue"
      )
      else ()
    ) return $t' passing myxmlcolumn returning content)

    from mytable
union all
select 
 XMLQUERY(
'copy $t := . modify(
 for $node in  $t/Warehouse/ZZZZ
  return replace value of node $node with "mynewvalue"
) return $t' passing myxmlcolumn returning content) from mytable;

Upvotes: 1

Related Questions