SQL处理XML字段DEMO

DEMO表中存储的xml字段内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<root default-locale="en_US" available-locales="en_US">
    <dynamic-element instance-id="bvnT4QI6" name="filed_Name" type="boolean" index-type="text">
        <dynamic-content><![CDATA[field content]]></dynamic-content>
    </dynamic-element>
</root>

1. EXTRACTVALUE - 查询xml中具体的filed:

SELECT -- XMLTYPE(table_field_name) 
EXTRACTVALUE(XMLTYPE(xml), '/root/dynamic-element[@name='filed_name']/dynamic-content/text()') 
FROM DEMO； 
-- EXTRACTVALUE也可以用到where字句中 

2. UPDATEXML - 更新xml中的具体filed:

UPDATE DEMO  
SET XML= UPDATEXML(XMLTYPE(xml), '/root/dynamic-element[@name='filed_name']/dynamic-content/text()', to_char(update_content)).getClobVal() 
-- demo表中xml field type 是 clob 
WHERE ...;