By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (xml_value CLOB);
INSERT INTO table_name (xml_value)
SELECT EMPTY_CLOB() || '<AddressDetails>
<SourceAddressID>41</SourceAddressID>
<SourceAddressLevel>BROKER</SourceAddressLevel>
<SourceAddressLevelID>41</SourceAddressLevelID>
<SourceAddressLevelType>BUSINESS</SourceAddressLevelType>
<AddressType>BUSINESS</AddressType>
<Address1>10 Tower Lane</Address1>
<City>MUM</City>
<State>MH</State>
<ZipCode>06001</ZipCode>
<Country>IN</Country>
<PrimaryAddressYN>Y</PrimaryAddressYN>
</AddressDetails>' FROM DUAL UNION ALL
SELECT EMPTY_CLOB() || '<AddressDetails>
<SourceAddressID>11</SourceAddressID>
<SourceAddressLevel>INSURED</SourceAddressLevel>
<SourceAddressLevelID>11</SourceAddressLevelID>
<SourceAddressLevelType>BUSINESS</SourceAddressLevelType>
<AddressType>BUSINESS</AddressType>
<Address1>Remond Street</Address1>
<City>HYD</City>
<State>HY</State>
<ZipCode>05250</ZipCode>
<Country>IN</Country>
<PrimaryAddressYN>Y</PrimaryAddressYN>
</AddressDetails>' FROM DUAL UNION ALL
SELECT EMPTY_CLOB() || '<AddressDetails>
<SourceAddressID>1</SourceAddressID>
<SourceAddressLevel>RISK</SourceAddressLevel>
<SourceAddressLevelID>1</SourceAddressLevelID>
<SourceAddressLevelType>LOCATION</SourceAddressLevelType>
<AddressType>BUILDING</AddressType>
<Address1>46 Main St</Address1>
<City>BENG</City>
<State>KT</State>
3 rows affected
UPDATE table_name
SET xml_value = XMLSERIALIZE(
DOCUMENT
XMLQUERY(
'copy $i := $x modify (
for $j in $i/AddressDetails/SourceAddressLevelType
return replace value of node $j with $new_type
)
return $i'
PASSING XMLTYPE(xml_value) AS "x",
'INSURED' AS "new_type"
RETURNING CONTENT
)
AS CLOB
INDENT SIZE = 4
)
WHERE XMLEXISTS(
'$x/AddressDetails[SourceAddressLevel="INSURED"]'
PASSING XMLTYPE(xml_value) AS "x"
);
1 rows affected
SELECT * FROM table_name
| XML_VALUE |
|---|
| <AddressDetails> <SourceAddressID>41</SourceAddressID> <SourceAddressLevel>BROKER</SourceAddressLevel> <SourceAddressLevelID>41</SourceAddressLevelID> <SourceAddressLevelType>BUSINESS</SourceAddressLevelType> <AddressType>BUSINESS</AddressType> <Address1>10 Tower Lane</Address1> <City>MUM</City> <State>MH</State> <ZipCode>06001</ZipCode> <Country>IN</Country> <PrimaryAddressYN>Y</PrimaryAddressYN> </AddressDetails> |
| <AddressDetails> <SourceAddressID>11</SourceAddressID> <SourceAddressLevel>INSURED</SourceAddressLevel> <SourceAddressLevelID>11</SourceAddressLevelID> <SourceAddressLevelType>INSURED</SourceAddressLevelType> <AddressType>BUSINESS</AddressType> <Address1>Remond Street</Address1> <City>HYD</City> <State>HY</State> <ZipCode>05250</ZipCode> <Country>IN</Country> <PrimaryAddressYN>Y</PrimaryAddressYN> </AddressDetails> |
| <AddressDetails> <SourceAddressID>1</SourceAddressID> <SourceAddressLevel>RISK</SourceAddressLevel> <SourceAddressLevelID>1</SourceAddressLevelID> <SourceAddressLevelType>LOCATION</SourceAddressLevelType> <AddressType>BUILDING</AddressType> <Address1>46 Main St</Address1> <City>BENG</City> <State>KT</State> <ZipCode>05250</ZipCode> <Country>IN</Country> </AddressDetails> |