xmloracle-databaseupdatexml

Oracle: how to use updateXML to update multiple nodes in a document?

I can write:

update my_table
set xml = updateXML(xml, '/a/b', '1')
where document_id = 123

Now what if in the same update query I also want to set /a/c to 2 (in addition /a/b to 1)? I am tempted to write:

update my_table
set 
    xml = updateXML(xml, '/a/b', '1'),
    xml = updateXML(xml, '/a/c', '2')
where document_id = 123

But this give me a "ORA-00957: duplicate column name". Any suggestion on how to do this?

Solution

  • The documentation indicates that the XPath string and expression can be repeated http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions205.htm#i1134878

    so try

    update my_table set xml = updateXML(xml, '/a/b', '1', '/a/c', '2') where document_id = 123