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>

Alex Poole · Accepted Answer · 2018-07-13T10:01:31.407

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.

edited Jul 13 '18 at 10:01

answered Jul 12 '18 at 17:21

Alex Poole

183,384
11
179
318

Weird, it did not work :/ After the two requests, the XML is still empty : – user2178964 Jul 13 '18 at 07:57
fpCodeActiviteArty – user2178964 Jul 13 '18 at 08:00
It works in the fiddle with your sample. I have no idea what you are doing differently, I can't see the original XML or the actual update you are doing. Maybe your `ns2` definition doesn't quite match or something, that would silently do nothing. But that's just a guess obviously. – Alex Poole Jul 13 '18 at 08:06
I modify the fiddle here to put the entire XML, but the string is too long : https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=6a6e883d13d05f1b86b5c3f807a6e1ac The name of the formProperty I cant to modify is : name="fpCodeActiviteArty" Also, I just see that even if your solutions works, in the case of an empty textValue node, it does not keep the XXX, it put directly the value "78910" like that 78910. – user2178964 Jul 13 '18 at 08:10
In fact, if I just put the real declaration of namespace in my samples in your fiddle here, it does not work anymore : https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=2ebfc462cfe76bf15149d606a58095d5 – user2178964 Jul 13 '18 at 08:18
I edited the original post, your solution almost works but I have a "Déjà vu" problem with the new textValue node created, no ns2 prefix, and an empty xmlns attribute... Thanks ! – user2178964 Jul 13 '18 at 08:25
My fiddle didn't work with your real namespace because the default is the same path as `ns2`; I wondered why your question didn't specify `ns2:` in the updateXML call. Yeah, I missed that the Value was set instead of textValue. [This is as close as I can get at the moment](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b794e5f95f1e7c4af17e369732035535). Redeclaring the `ns2` in the new element is ugly but valid. – Alex Poole Jul 13 '18 at 08:49
I've added an XMLQuery update version that doesn't duplicate the namespace declaration. – Alex Poole Jul 13 '18 at 09:38
Great, the last proposition works perfectly ! Thank you – user2178964 Jul 16 '18 at 12:26

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

1 Answers1