Select and Update Oracle BLOB column with XMLQUERY

For selecting from XML you can either use ExtractValue(XmlType, XPath) or XmlTable to transform the Xml clob into a queriable table of XML. For BLOB converstion, you should be able to just wrap it with XmlType(blob_value, 1), then you can perform any of the XML related function on it.

SELECT ExtractValue(
          XmlType('<test><node1>value1</node1><node2>value2</node2></test>'), 
                  '/test/node1') as Node1 
FROM dual;

Or using XmlTable

SELECT xt.Node1, xt.Node2
FROM XmlTable('/test/block'
         PASSING XmlType('<test>
<block><node1>value1a</node1><node2>value2a</node2></block>
<block><node1>value1b</node1><node2>value2b</node2></block>
<block><node1>value1c</node1><node2>value2c</node2></block>
</test>')
        COLUMNS
        "Node1"     VARCHAR2(20)   PATH 'node1',
        "Node2"     VARCHAR2(20)   PATH 'node2') AS xt;

Using UpdateXml, assuming the record I am updating has the above XML in a column:

UPDATE MyTable SET xml_data =
UpdateXml(xml_data, '/test/block/node2[text() = "value2b"]/text()', 'value2z')
WHERE data_id = 1;

The above should update the node2 that had the value value2b to now have value2z instead. Which then returns the new XML and assigns it to the column xml_data in the record that matches data_id = 1.

One note, in the above query, it is working with a column that is already of the type XmlType. You are working with a BLOB. I would ask, is there a reason for it being BLOB instead of CLOB or XmlType? If you are storing VARCHAR type data you should really be using one of the latter two types, CLOB if you are storing various VARCHAR data, and XmlType (which is a more specific type of CLOB anyway) if you are storing strictly XML data.

If you are stuck using the BLOB data type you will need to perform a lot of conversions. Using XmlType(blob_data, 1) should get you from BLOB to XmlType, but going back you will likely need to use UTL_RAW.CAST_TO_RAW(xml_data). So the query would become:

UPDATE MyTable SET clob_data =
UTL_RAW.CAST_TO_RAW(
    UpdateXml(XmlType(clob_data, 1), '/test/block/node2[text() = "value2b"]/text()', 'value2z').GetClobVal()
)
WHERE data_id = 1;

Here is a working stand alone example showing the various methods mentioned above:

DECLARE varchar_data    VARCHAR2(500);
        blob_data       BLOB;
        xml_data        XMLType;
        node1Val        VARCHAR(20);
        node2Val        VARCHAR(20);

BEGIN
    select '<test>
<group><node1>value1a</node1><node2>value2a</node2></group>
<group><node1>value1b</node1><node2>value2b</node2></group>
<group><node1>value1c</node1><node2>value2c</node2></group>
<group><node1>value1d</node1><node2>value2d</node2></group>
</test>' into varchar_data from dual;

    select UTL_RAW.CAST_TO_RAW(varchar_data) into blob_data from dual;

    select XmlType(blob_data, 1) into xml_data from dual;
    dbms_output.put_line(xml_data.getClobVal());

    select xt.Node1, xt.Node2
    into node1Val, node2Val
    from XmlTable('/test/group' 
        passing XmlType(blob_data, 1)
        columns Node1     VARCHAR2(20)    path 'node1',
                Node2     VARCHAR2(20)    path 'node2'
        ) xt
    where xt.Node1 = 'value1c';
    dbms_output.put_line('node1Val = ''' || node1Val || ''', node2Val = ''' || node2Val || ''';'); 

    -- Using UpdateXml to update the XML, that will return an XmlType 
    -- so we call GetClobVal() to let CAST_TO_RAW convert to BLOB.
    select UTL_RAW.CAST_TO_RAW(
        UpdateXml(
            XmlType(blob_data, 1), 
            '/test/group/node2[../node1/text() = "value1c"]/text()', 
            'zzzz').GetClobVal()
        ) into blob_data
    from dual; 

    select XmlType(blob_data, 1) into xml_data from dual;
    dbms_output.put_line(xml_data.getClobVal());

    select xt.Node1, xt.Node2
    into node1Val, node2Val
    from XmlTable('/test/group' 
        passing XmlType(blob_data, 1)
        columns Node1     VARCHAR2(20)    path 'node1',
                Node2     VARCHAR2(20)    path 'node2'
        ) xt
    where xt.Node1 = 'value1c';
    dbms_output.put_line('node1Val = ''' || node1Val || ''', node2Val = ''' || node2Val || ''';'); 

END;