我有一個具有以下結構的 xml，它存盤為表中的 clob 列。
我必須將 Zipcode 和 City 欄位更新為 1000 和 Abcd，只要現有節點包含 Zipcode 95813。
可以有多個具有相同城市和郵政編碼的地址節點。
請幫助我，在此先感謝。
<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>
預期產出
<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>
我已經嘗試過 updatexml 功能，但沒有找到根據 updatexml 中的條件更新特定節點的選項
uj5u.com熱心網友回復：
請嘗試以下解決方案。
Oracle 檔案建議使用 XQuery 更新 XML
db小提琴
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432945.html