Update XML value in stored in Clob-CodePudding

Does anyone know a solution how I can change a specific value within xml? XML is stored in Clob datatype.

My XML looks like:

<settings name="TEST_NAME" path="TEST_PATH">
<values>
    <value param="Version">20200207</value>
</values>
<collections>
    <collection name="Items">
        <values>
            <value param="TEST_PARAM">true</value>
        </values>
        <collections>
            </collection>
            <collection name="TEST_COL">
                <values>
                    <value param="DockedLeft">0</value>
                </values>
                <collections>
                    <collection name="ItemLink0">
                        <values>
                            <value param="ItemName">TEST_PARAM</value>
                        </values>
                    </collection>
                </collections>
            </collection>
    </collection>
</collections>

What I need to update is "TEST_PARAM" inside TEST_COL collection. Collection name itemlink0 can be different. Thanks for the answers!

CodePudding user response：

I created some sample XML that is valid and inserted into a CLOB column. It should suffice to show how this can be done using XMLQuery and a SQL update.

CREATE table xml_tbl (xml_str CLOB)

INSERT INTO xml_tbl VALUES(
'<settings name="TEST_NAME" path="TEST_PATH">
  <collections>
    <collection name="TEST_COL">
      <values>
        <value param="DockedLeft">0</value>
      </values>
      <collections>
        <collection name="ItemLink0">
          <values>
            <value param="ItemName">TEST_PARAM</value>
          </values>
        </collection>
      </collections>
    </collection>
  </collections>
</settings>')

The element value can be updated by converting the CLOB to/from XMLType and utilizing XMLQuery to update the XML. The element having the dynamic attribute name can be a PL/SQL variable within the PASSING clause of the XMLQuery.

DECLARE
  l_dyn_attr_name VARCHAR2(100):= 'ItemLink0';
  l_element_value VARCHAR2(100):= 'new value';
BEGIN

  UPDATE xml_tbl xt
  SET    xt.xml_str = 
     XMLTYPE.GETCLOBVAL(XMLQuery('copy $i := $x1 modify
                     (for $j in $i/settings/collections/collection[@name="TEST_COL"]/collections/collection[@name=$dynamic_attr_name]/values/value
                      return replace value of node $j with $new_elem_value)
                      return $i' PASSING XMLTYPE(xt.xml_str) AS "x1"
                                        ,l_dyn_attr_name AS "dynamic_attr_name"
                                        ,l_element_value AS "new_elem_value" RETURNING CONTENT));

  COMMIT;
END;