Update clob column with xml value in oracle-CodePudding

I have an xml with below structure which is stored as a clob column in a table.

I have to update the Zipcode and City fields to 1000 and Abcd wherever the existing node contains a Zipcode 95813.

There can be multiple address node with same city and Zipcode.

Please help me, Thanks in advance.

<Employee>
    <Name>John</Name>
    <Age>26</Age>
    <Addresses>
        <Address>
            <Street>Street1</Street>
            <City>Sacramento</City>
            <ZipCode>95813</ZipCode>
        </Address>
        <Address>
            <Street>Street2</Street>
            <City>Los Angeles</City>
            <ZipCode>95300</ZipCode>
        </Address>
    </Addresses>
</Employee>

expected output

<Employee>
    <Name>John</Name>
    <Age>26</Age>
    <Addresses>
        <Address>
            <Street>Street1</Street>
            <City>Abcd</City>
            <ZipCode>1000</ZipCode>
        </Address>
        <Address>
            <Street>Street2</Street>
            <City>Los Angeles</City>
            <ZipCode>95300</ZipCode>
        </Address>
    </Addresses>
</Employee>

I have already tried updatexml function but didn’t find an option to update specific nodes based on condition in updatexml

CodePudding user response：

Please try the following solution.

Oracle documentation recommends to use XQuery to update XML

db-fiddle

SQL

-- DDL and sample data population, start
CREATE TABLE HOLDS_XML
(xml_col XMLTYPE)
XMLTYPE xml_col STORE AS SECUREFILE BINARY XML;

INSERT INTO HOLDS_XML (xml_col)
VALUES (xmltype('<Employee>
    <Name>John</Name>
    <Age>26</Age>
    <Addresses>
        <Address>
            <Street>Street1</Street>
            <City>Sacramento</City>
            <ZipCode>95813</ZipCode>
        </Address>
        <Address>
            <Street>Street2</Street>
            <City>Los Angeles</City>
            <ZipCode>95300</ZipCode>
        </Address>
    </Addresses>
</Employee>'));
-- DDL and sample data population, end

-- before
select * from  HOLDS_XML;

UPDATE HOLDS_XML
SET xml_col = XMLQuery('copy $i := $p1 
modify(
  replace value of node $i/Employee/Addresses/Address[ZipCode="95813"]/City with $p2,
  replace value of node $i/Employee/Addresses/Address[ZipCode="95813"]/ZipCode with $p3
)
return $i'
PASSING xml_col AS "p1", 'Abcd' AS "p2", '1000' AS "p3"
RETURNING CONTENT)
WHERE XMLExists('$p/Employee/Addresses/Address[ZipCode="95813"]'
PASSING xml_col AS "p"
);

-- after
select * from  HOLDS_XML;