Update clob column with xml value in oracle

Question

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

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your RDBMS version. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Feb 21, 2022 at 16:49

Yitzhak Khabinsky · Accepted Answer · 2022-02-21 18:21:38Z

1

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;

edited Feb 21, 2022 at 18:21

answered Feb 21, 2022 at 17:44

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Poole Over a year ago

Also works with a CLOB (rather than XMLType) column, which seems to be what the OP is saying, with minor tweaks - db<>fiddle, using XMLSerialize rather than getCLOB to keep indentation.

Collectives™ on Stack Overflow

Update clob column with xml value in oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related