UPDATEXML throws SQL Error: ORA-00927: missing equal sign when attempting to convert and update CLOB datatype

Question

I worked through the example through the Oracle documentation page for the UPDATEXML function: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions205.htm

Now, I'm trying to apply this to the data in my database. The column in my database is CLOB (used for multiple things), so I need to convert my data to the xmltype before performing xml related operations. However, my attempt is returning the following error SQL Error: ORA-00927: missing equal sign.

Is there a way to update the clob-stored xml without modifying the column data type?

Attempt

Reusing the Oracle example with datatypes of my database.
1. Creating table

CREATE TABLE xwarehouses
(  
  MSD_ID INTEGER,
  CDATA CLOB
);

2. Adding data

INSERT INTO xwarehouses VALUES 
  (1,
  ('<?xml version="1.0"?>
   <Warehouse>
    <WarehouseId>1</WarehouseId>
    <WarehouseName>Southlake, Texas</WarehouseName>
    <Building>Owned</Building>
    <Area>25000</Area>
    <Docks>2</Docks>
    <DockType>Rear load</DockType>
    <WaterAccess>true</WaterAccess>
    <RailAccess>N</RailAccess>
    <Parking>Street</Parking>
    <VClearance>10</VClearance>
   </Warehouse>'));

3. Converting CLOB to XMLTYPE to extract value from specified tag

SELECT EXTRACTVALUE(XMLTYPE(cdata),'/Warehouse/WaterAccess') as VAL from xwarehouses;

VAL | true

4. Attempt to update value using same conversion in UPDATE

update XWAREHOUSES
set XMLTYPE(cdata) = 
    updatexml(XMLTYPE(cdata),'/Warehouse/WaterAccess/text()','false')
where msd_id = 1;

**5. This returns the error: SQL Error: ORA-00927: missing equal sign

I think set XMLTYPE(cdata) = should be set cdata = and assuming the right side of the set will be implicitly converted to CLOB. Give a try and let me know. — Ted at ORCL.Pro
– Ted at ORCL.Pro, Commented Oct 2, 2017 at 20:17
If I remove XMLTYPE in the SET, but leave it in the XMLUPDATE, I get SQL Error: ORA-00932: inconsistent datatypes: expected CLOB got - 00932. 00000 - "inconsistent datatypes: expected %s got %s". — sikrut
– sikrut, Commented Oct 2, 2017 at 20:43
If I remove the XMLTYPE on both side i get: SQL Error: ORA-00932: inconsistent datatypes: expected - got - 00932. 00000 - "inconsistent datatypes: expected %s got %s" — sikrut
– sikrut, Commented Oct 2, 2017 at 20:44
Do you mean is writing some form of a stored proc an option here instead of a direct update? — sikrut
– sikrut, Commented Oct 2, 2017 at 20:45

sikrut · Accepted Answer · 2017-10-02 22:07:15Z

Ok, I solved this 2 different ways, both basically the same way, but one is query and one is a script.

Solution 1

load the data as xmltype, then perform xml operation, and finally convert back to clob before executing the update

update xwarehouses
set cdata =
(
  select updatexml(xmltype(cdata),'/Warehouse/WaterAccess/text()','true').getclobval() 
  from xwarehouses where msd_id = 1
)
where msd_id = 1;
commit;

Solution 2

This isn't the tidiest version, as I could probably make due with the query from solution 1 and just add some variables. This essentially does the same thing with all the conversions listed out.

DECLARE
  p_msg_id number := 1;
  p_cdata xmltype;  
  p_value varchar(50) :='false';
  p_data clob;

BEGIN
  select cdata into p_data from xwarehouses;
  p_cdata := xmltype(p_data);
  select updatexml(p_cdata,'/Warehouse/WaterAccess/text()',p_value) into p_cdata from dual;
  p_data := p_cdata.getclobval();
  update xwarehouses
  set cdata = p_data
  where msd_id = p_msg_id;
  commit;

END;

Arkadiusz Łukasiewicz · Accepted Answer · 2017-10-03 07:10:20Z

1

From ORACLE 11

All Oracle SQL functions for updating XML data are deprecated. Oracle recommends that you use XQuery Update instead. These are the deprecated XML updating functions:

Migration from Oracle Functions for Updating XML Data to XQuery Update

Example:

UPDATE xwarehouses SET cdata =
  xmlserialize(document XMLQuery('copy $tmp := $source  modify
            (for $i in $tmp/Warehouse/WaterAccess/text()  
             return replace value of node $i
                    with "false") 
            return $tmp'
           PASSING xmltype(cdata) as "source"  RETURNING CONTENT))
  WHERE cdata IS NOT NULL;

answered Oct 3, 2017 at 7:10

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Collectives™ on Stack Overflow

UPDATEXML throws SQL Error: ORA-00927: missing equal sign when attempting to convert and update CLOB datatype

Attempt

2 Answers 2

Solution 1

Solution 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Attempt

2 Answers 2

Solution 1

Solution 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related