Oracle SQL UPDATEXML Replace node value even if it's empty

Question

I have one CLOB database column which contain a big XML : XML_CONF I usually use the function updateXML to modify specific node of the XML, it works great. But today, I have a lot of trouble with it, because the nodes I want to modify are sometimes empty, and it does not work in this case...

Example of XML with empty textValue :

<ns2:ConfigurationTree xmlns:ns2="com.xxxx" xmlns="com.xxxx.yyyy">
<ns2:ConfigurableProduct ...>...
    <ns2:CPNode name="cpX">...
        <ns2:FormProperty name="fpX">
            <ns2:SingleValuation isCommentUserChoice="false" isValueUserChoice="false" isQtyUserChoice="false">
                <ns2:Value>
                    **<ns2:textValue/>**
                </ns2:Value>
            </ns2:SingleValuation>
        </ns2:FormProperty>
    </ns2:CPNode>
</ns2:ConfigurableProduct>

Example of XML with textValue :

<ns2:ConfigurationTree xmlns:ns2="com.xxxx" xmlns="com.xxxx.yyyy">
<ns2:ConfigurableProduct ...>...
    <ns2:CPNode name="cpX">...
        <ns2:FormProperty name="fpX">
            <ns2:SingleValuation isCommentUserChoice="false" isValueUserChoice="false" isQtyUserChoice="false">
                <ns2:Value>
                    **<ns2:textValue>123456</ns2:textValue>**
                </ns2:Value>
            </ns2:SingleValuation>
        </ns2:FormProperty>
    </ns2:CPNode>
</ns2:ConfigurableProduct>

For example, to replace the textValue content by "78910", I tried this to handle the two cases :

update T_TABLE set XML_CONF = updatexml(xmltype(XML_CONF),
'//FormProperty[@name="fpX"]//Value/textValue',xmltype('<textValue>78910</textValue>'),
'//FormProperty[@name="fpX"]//Value/textValue/text()','78910',
'xmlns:ns2="com.xxxx" xmlns="xxxx.yyyy"').getClobVal();

But the result broke the XML (no more prefix and xmlns empty in node) :

<ns2:ConfigurationTree xmlns:ns2="com.xxxx" xmlns="com.xxxx.yyyy">
<ns2:ConfigurableProduct ...>...
    <ns2:CPNode name="cpX">...
        <ns2:FormProperty name="fpX">
            <ns2:SingleValuation isCommentUserChoice="false" isValueUserChoice="false" isQtyUserChoice="false">
                <ns2:Value>
                    **<textValue xmlns="">78910</textValue>**
                </ns2:Value>
            </ns2:SingleValuation>
        </ns2:FormProperty>
    </ns2:CPNode>
</ns2:ConfigurableProduct>

And if I recall the same request, with a different textValue, it does not update nothing anymore after that... I think it's because the prefix is broken on the node...

I try to do it with XMLQuery (Oracle 12), but it's the same problem.

EDIT

It almost works with :

    update T_TABLE set XML_CONF = updatexml(xmltype(XML_CONF),
  '//FormProperty[@name="fpX"]//Value/textValue/text()','78910',
  '//FormProperty[@name="fpX"]//Value/textValue[not(text())]',xmltype('<textValue>78910</textValue>'),
  xmlns:ns2="com.xxxx" xmlns="xxxx.yyyy"' ).getClobVal();

But in output I don't have the new ns2:textValue node, I only have :

<ns2:Value><textValue xmlns="">78910</textValue></ns2:Value>

Why does it break the ns2 prefix, and why it put an empty xmlns attribute ?

If I specify the namespace in the new node it works, but it seems useless because they are already declared in the root node :

    update T_TABLE set XML_CONF = updatexml(xmltype(XML_CONF),
  '//FormProperty[@name="fpX"]//Value/textValue/text()','78910',
  '//FormProperty[@name="fpX"]//Value/textValue[not(text())]',xmltype('<ns2:textValue xmlns:ns2="com.xxxx" xmlns="xxxx.yyyy">78910</ns2:textValue>'),
  'xmlns:ns2="com.xxxx" xmlns="xxxx.yyyy"' ).getClobVal();

Give :

<ns2:textValue xmlns:ns2="com.xxxx" xmlns="xxxx.yyyy">78910</ns2:textValue></ns2:Value>

Answer 1

You could do:

update T_TABLE set XML_CONF = updatexml(xmltype(XML_CONF),
  '//ns2:FormProperty[@name="fpX"]//ns2:Value/ns2:textValue/text()','78910',
  '//ns2:FormProperty[@name="fpX"]//ns2:Value/ns2:textValue[not(text())]',
     xmlelement("ns2:textValue", xmlattributes('com.xxxx' as "xmlns:ns2"), '78910'),
  'xmlns:ns2="com.xxxx" xmlns="com.xxxx.yyyy"').getClobVal();

to identify either a text node or a node with no text; or if ns2 is the same as the default (from comments):

update T_TABLE set XML_CONF = updatexml(xmltype(XML_CONF),
  '//FormProperty[@name="fpX"]//Value/textValue/text()','78910',
  '//FormProperty[@name="fpX"]//Value/textValue[not(text())]',
     xmlelement("ns2:textValue", xmlattributes('com.xxxx' as "xmlns:ns2"), '78910'),
  'xmlns:ns2="com.xxxx" xmlns="com.xxxx"').getClobVal();

db<>fiddle with your real namespace. The newly-created textValue node redeclares ns2 but functionally that shouldn't matter.

Of course, updateXML is deprecated in 12c, but you should be able to do the same thing with Xquery update. In fact that's simpler:

update t_table set xml_conf = xmlquery(
   'copy $d := .
    modify (
      for $i in $d//*:FormProperty[@name="fpX"]//*:Value/*:textValue
        return replace value of node $i with $newValue
    )
    return $d'
    passing xmltype(xml_conf), '78910' as "newValue"
    returning content
  ).getClobVal();

I've wildcarded the namespaces for simplicity (well, actually, I haven't figure out how to make it work with namespace prefixes, even if ns2 is different from default). For some reason that gets "ORA-19112: error raised during evaluation: XQuery Update connot be compiled" on both db<>fiddle and SQL Fiddle, which are both 11.20.02; but works fine on my 11.2.0.4 and 12.2.0.1 databases.

You can add a check for the relevant node existing to avoid unneccessary updates.