0

I have this situation

create table FOO (
BAR clob
);

create table FOO2 (
VALUE_OLD varchar2(255),
VALUE_NEW varchar2(255)
);

insert into FOO2 values (1, 10);
insert into FOO2 values (2, 20);
insert into FOO2 values (3, 30);

insert into FOO (BAR) values (
'<xml>
  <ope cod_ope="123" cod_sogg="1"/>
  <ope cod_ope="456" cod_sogg="2"/>
  <ope cod_ope="789" cod_sogg="3"/>
</xml>'
);

And I need to update the attribute cod_sogg in every ope tag with the value_new in the table FOO2 (note that the number of ope tag is unknown, there could be 100 ope tags) so the xml result should be

<xml>
  <ope cod_ope="123" cod_sogg="10"/>
  <ope cod_ope="456" cod_sogg="20"/>
  <ope cod_ope="789" cod_sogg="30"/>
</xml>

I tryed with something like

update FOO set BAR = UPDATEXML(
  xmltype(BAR),
  '/xml/ope/@cod_sogg', 
  (
    select VALUE_NEW from FOO2 where value_old = extractvalue(xmltype(BAR), '/xml/ope/@cod_sogg')
  )
  ).getClobVal();

But obviously i get

Errore SQL: ORA-19025: EXTRACTVALUE restituisce il valore di un solo nodo
19025. 00000 -  "EXTRACTVALUE returns value of only one node"
*Cause:    Given XPath points to more than one node.
*Action:   Rewrite the query so that exactly one node is returned.

How can I do this? Thanks

Ale4224
  • 11
  • 2
  • Does this help? [Update xml tag in a CLOB column in Oracle](https://stackoverflow.com/questions/31002954/update-xml-tag-in-a-clob-column-in-oracle) – Abra Jun 22 '20 at 12:28
  • Not really, the problem here is to update multiple attribute with the same xpath – Ale4224 Jun 22 '20 at 12:32

1 Answers1

0

You can use anonymous block to do so.

BEGIN
FOR I IN (SELECT VALUE_OLD, VALUE_NEW FROM FOO2)
LOOP
UPDATE FOO
SET BAR=to_clob(updatexml( xmltype.createxml(BAR),
                          '/xml/ope[@cod_sogg="' || I.VALUE_OLD || '"]/@cod_sogg',I.VALUE_NEW));
END LOOP;
END;
/

db<>fiddle demo

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • this works, thanks, but I'd rather not to cycle the FOO2 table, I'll have thousands of record in that table – Ale4224 Jun 22 '20 at 12:48