How do I add an attribute to xml contained within a CLOB in an Oracle database?

Question

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

Answer 1

You could use a combination of deleteXml() along with either appendChildXml(), insertChildXml(), or insertXmlBefore() to remove the existing node and then re-add it back with the new attribute 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

Simple Oracle SQL to add "attrname=attrval" to all mynode elements in the clobcol column xml in every row in mytable

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