How to add attribute to xml contained in CLOB in Oracle database?

Question

How to add attribute to xml contained in CLOB in Oracle database? I can use the UpdateXML function to update an existing attribute, but it will not add it.

Answer 1

You can use the combination deleteXml()with appendChildXml(), insertChildXml()or insertXmlBefore(), to delete an existing node, and then add it back again with the new attribute that is now included.

Answer 2

with t as (
    select 
        xmltype('<a><b c="2">1</b></a>') x,
        '/a/b' node,   --node where attribute located
        '@d' att,      --attribute name
        'new' val      --new value
    from dual
)
select 
    x,
    insertchildxml(deletexml(x,node||'/'||att), node, att, val) x_new
from t

Answer 3

Oracle SQL "attrname = attrval" mynode xml clobcol mytable

update mytable s set
  s.clobcol = insertchildxml(xmltype(s.clobcol)
                            ,'//mynode'
                            ,'@attrname'
                            ,'attrval'
                            ).getclobval();